Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't want to hard-code file name in macro
I'm writing a basic macro to do following:
* Have a primary workbook that has a data tab used in a pivot in same workbook which is where macro will be executed from * Have a second workbook that has data that is copied and pasted onto data tab in primary workbook * After copying and pasting data from second to primary, want to close the second workbook and leave cursor on updated pivot tab in primary workbook 3 questions: 1. Macro is good from standpoint of allowing user to browse and select file and open copy copy data but fails in that the next line of code is: Windows ("actualfilename.xlsm"). activate ... The minute this file is saved as anything else, the macro will fail as the name will be different than what is stored. How can I make the macro flexible? 2. The other place where I need help is in closing the second workbook. I had already used filename when allowing user to browse and select the file. So I was hoping that is stored and thus something I could use in my code. It wasn't working so I removed but am hoping someone can help me figure out how to incorporate. 3. The second file will have varying rows over time (first time I executed, there were 37 rows) ... the second time, it was double however the copy/paste only brought in the 37 rows. How can I make sure copy is always of all info? Below is the VBA as is right now if that helps... Thanks to any advice Gretchen VBA CODE: Sub GetARMSExport() Application.ScreenUpdating = False Dim Finfo As String Dim FilterIndex As Integer Dim title As String Dim filename As Variant 'set up list of file filters Finfo = "Text Files (*.txt),*.txt," & "Lotus Files (*.prn),*.prn," & "Comma Separted Files (*.csv),*.csv," & "ASCII Files (*.asc),*.asc," & "All Files (*.*),*.*" 'Display *.* by default FilterIndex = 5 'Set the dalog box caption title = "Select a File to Import" 'Get the filename filename = Application.GetOpenFilename(Finfo, _ FilterIndex, title) 'Handle return info from dialog box If filename = False Then MsgBox "No file was selected." Else Workbooks.Open filename Cells.Select Selection.Copy Windows("B2A_E2C Tool.fy2012 - revised draft unprotected - 021512 v4.xlsm"). _ Activate Sheets("ARMS Detailed Scheduling Report").Select Range("A1").Select ActiveSheet.Paste Range("A1").Select Sheets("ARMS Summary Scheduled Hrs").Select Application.CutCopyMode = False ActiveSheet.PivotTables("PivotTable4").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable4").PivotCache. Refresh Calculate Range("A6").Select Application.ScreenUpdating = True End If End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hard code the workbook name in macro | Excel Programming | |||
hard code a password in a vba code | Excel Programming | |||
how do find the actual macro file on hard drive to send? | Excel Programming | |||
Not Hard Code If | Excel Discussion (Misc queries) | |||
Can I use code/macro to change code/macro in an existing file? | Excel Programming |