By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,645 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

How to print to file within the same Excel workbook.

P: 34
Hello,

I've already created a dropdown so an end user can pull up a specific file, and this will create a link to a variety of files (.xls, .txt., .pdf, ect...).

I'd like to set about twenty files within the given folder to transfer to a specific sheet within the same workbook.

I.e. - One file is "Gross to Net.xls", and once the user selects the dropdown the "Gross to Net.xls" information will be automatically copied and pasted to a sheet (labeled "GTN"). This should happen for all twenty files which will have the same sheet name, so next time the user drops down on another file, different information is populated in each of the 20 sheets.

So far I can pull up the folder for the user to select the file, but I would like VBA to copy/paste for them:
Expand|Select|Wrap|Line Numbers
  1. Sub GrossToNet()
  2.     ActiveSheet.Shapes("AutoShape 109").Select
  3.     Selection.Characters.Text = "GrossToNetSummary.                                       xls"
  4.  
  5.     Dim fn As Variant
  6.     ChDrive "T:\"
  7.     ChDir ([GTN])
  8.     fn = Application.GetOpenFilename("All files,*.*,XLS Files,*.xls,", _
  9.         1, "Technician Technical Information - Select folder and file to open", , False)
  10.     If TypeName(fn) = "Boolean" Then Exit Sub
  11.     ' the user didn't select a file
  12.     Debug.Print "Selected file: " & fn
  13.     Select Case Right(fn, 3)
  14.  
  15.     Case Is = "xls"
  16.         ActiveWorkbook.FollowHyperlink Address:=fn
  17.     Case Else
  18.         MsgBox "Please select an Excel file."
  19.     End Select
  20.  
  21. Range("a1").Select
  22. End Sub
Apr 25 '08 #1
Share this Question
Share on Google+
2 Replies


Dököll
Expert 100+
P: 2,364
Hello,

I've already created a dropdown so an end user can pull up a specific file, and this will create a link to a variety of files (.xls, .txt., .pdf, ect...).
...

...

So far I can pull up the folder for the user to select the file, but I would like VBA to copy/paste for them:

Expand|Select|Wrap|Line Numbers
  1. Sub GrossToNet()
  2.     ActiveSheet.Shapes("AutoShape 109").Select
  3.     Selection.Characters.Text = "GrossToNetSummary.                                       xls"
  4.  
  5.     Dim fn As Variant
  6.     ChDrive "T:\"
  7.     ChDir ([GTN])
  8.     fn = Application.GetOpenFilename("All files,*.*,XLS Files,*.xls,", _
  9.         1, "Technician Technical Information - Select folder and file to open", , False)
  10.     If TypeName(fn) = "Boolean" Then Exit Sub
  11.     ' the user didn't select a file
  12.     Debug.Print "Selected file: " & fn
  13.     Select Case Right(fn, 3)
  14.  
  15.     Case Is = "xls"
  16.         ActiveWorkbook.FollowHyperlink Address:=fn
  17.     Case Else
  18.         MsgBox "Please select an Excel file."
  19.     End Select
  20.  
  21. Range("a1").Select
  22. End Sub
Greetings, deve8ore!

I am not sure how this can be handled, I will however send over to our VBA forum for further support.

Also, if you have some time, I would fetch through Search box up above, right, perhaps something was already posted on this or something similar:-) Might give you other ideas in the end...

Good luck with the project:-)

Dököll
May 3 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Most of your posts on this subject have been listed in the Visual Basic forum; are you in fact trying to do this from within an MS Access database?

Linq ;0)>
May 4 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.