Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open File then Set as
Dim sFile
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then Set xlBook = Workbook.Open(sFile) End If -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MacroLearning" wrote in message ... Hi all, I have a macro that works as I want. When I first started building I included the original filename to copy from. That file will change every month. So I want the user to open the file, when the file is opened, it will become "xlBook1". following is the code. I'm getting a debug at that point, but am not sure how to get past it. Any help will be very much appreciated! 'Book 1 is Headcount by Detail Report Dim xlBook1 As Excel.Workbook 'Book 2 is Quirk (all except SSGA) Dim xlBook2 As Excel.Workbook 'Sheet7 is HC Summary worksheet from HC Detail Report Dim xlSheet7 As Excel.Worksheet 'Sheet8 is HC Summary worksheet for Quirk Dim xlSheet8 As Excel.Worksheet 'Copy and Paste from Headcount Detail to Quirk Set xlBook1 = Application.GetOpenFilename <=====This is the problem here Set xlSheet1 = xlBook1.Worksheets("Headcount Detail") Set xlBook2 = Workbooks.Open( _ "C:\\Distribution File Cuts\Headcount by Title - Quirk.xls") Set xlSheet2 = xlBook2.Worksheets("Headcount Detail") Set xlSheet7 = xlBook1.Worksheets("Headcount Summary") Set xlSheet8 = xlBook2.Worksheets("Headcount Summary") xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1") xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1") xlSheet2.Rows("40:47").Delete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open File then Set as
Do you have ann. application object variable?
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "MacroLearning" wrote in message ... Thank you for posting an answer to my question. I am still getting a "debug" when I try to execute the macro. It won't set the opened file as xlbook. I get a 424 error code. I changed it to include your suggestion. Dim sFile sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then Set xlBook = Workbook.Open(sFile) <<<========== this is where it hangs up at Set xlSheet1 = xlBook.Worksheets("Headcount Detail") Set xlBook2 = Workbooks.Open( _ "W:\\Distribution File Cuts\Headcount by Bank Title - Quirk.xls") Set xlSheet2 = xlBook2.Worksheets("Headcount Detail") Set xlSheet7 = xlBook1.Worksheets("Headcount Summary") Set xlSheet8 = xlBook2.Worksheets("Headcount Summary") xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1") xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1") xlSheet2.Rows("40:47").Delete Any help would be greatly appreciated. "Bob Phillips" wrote: Dim sFile sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then Set xlBook = Workbook.Open(sFile) End If -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MacroLearning" wrote in message ... Hi all, I have a macro that works as I want. When I first started building I included the original filename to copy from. That file will change every month. So I want the user to open the file, when the file is opened, it will become "xlBook1". following is the code. I'm getting a debug at that point, but am not sure how to get past it. Any help will be very much appreciated! 'Book 1 is Headcount by Detail Report Dim xlBook1 As Excel.Workbook 'Book 2 is Quirk (all except SSGA) Dim xlBook2 As Excel.Workbook 'Sheet7 is HC Summary worksheet from HC Detail Report Dim xlSheet7 As Excel.Worksheet 'Sheet8 is HC Summary worksheet for Quirk Dim xlSheet8 As Excel.Worksheet 'Copy and Paste from Headcount Detail to Quirk Set xlBook1 = Application.GetOpenFilename <=====This is the problem here Set xlSheet1 = xlBook1.Worksheets("Headcount Detail") Set xlBook2 = Workbooks.Open( _ "C:\\Distribution File Cuts\Headcount by Title - Quirk.xls") Set xlSheet2 = xlBook2.Worksheets("Headcount Detail") Set xlSheet7 = xlBook1.Worksheets("Headcount Summary") Set xlSheet8 = xlBook2.Worksheets("Headcount Summary") xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1") xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1") xlSheet2.Rows("40:47").Delete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open File then Set as
Set xlBook = Workbook.Open(sFile) should be plural Set xlBook = Workbooks.Open(sFile) ' plural workbookS Don't feel bad. I did something almost identical to that this morning, but fortunately kept it to a private audience. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "MacroLearning" wrote in message ... Thank you for posting an answer to my question. I am still getting a "debug" when I try to execute the macro. It won't set the opened file as xlbook. I get a 424 error code. I changed it to include your suggestion. Dim sFile sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then Set xlBook = Workbook.Open(sFile) <<<========== this is where it hangs up at Set xlSheet1 = xlBook.Worksheets("Headcount Detail") Set xlBook2 = Workbooks.Open( _ "W:\\Distribution File Cuts\Headcount by Bank Title - Quirk.xls") Set xlSheet2 = xlBook2.Worksheets("Headcount Detail") Set xlSheet7 = xlBook1.Worksheets("Headcount Summary") Set xlSheet8 = xlBook2.Worksheets("Headcount Summary") xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1") xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1") xlSheet2.Rows("40:47").Delete Any help would be greatly appreciated. "Bob Phillips" wrote: Dim sFile sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then Set xlBook = Workbook.Open(sFile) End If -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MacroLearning" wrote in message ... Hi all, I have a macro that works as I want. When I first started building I included the original filename to copy from. That file will change every month. So I want the user to open the file, when the file is opened, it will become "xlBook1". following is the code. I'm getting a debug at that point, but am not sure how to get past it. Any help will be very much appreciated! 'Book 1 is Headcount by Detail Report Dim xlBook1 As Excel.Workbook 'Book 2 is Quirk (all except SSGA) Dim xlBook2 As Excel.Workbook 'Sheet7 is HC Summary worksheet from HC Detail Report Dim xlSheet7 As Excel.Worksheet 'Sheet8 is HC Summary worksheet for Quirk Dim xlSheet8 As Excel.Worksheet 'Copy and Paste from Headcount Detail to Quirk Set xlBook1 = Application.GetOpenFilename <=====This is the problem here Set xlSheet1 = xlBook1.Worksheets("Headcount Detail") Set xlBook2 = Workbooks.Open( _ "C:\\Distribution File Cuts\Headcount by Title - Quirk.xls") Set xlSheet2 = xlBook2.Worksheets("Headcount Detail") Set xlSheet7 = xlBook1.Worksheets("Headcount Summary") Set xlSheet8 = xlBook2.Worksheets("Headcount Summary") xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1") xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1") xlSheet2.Rows("40:47").Delete |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open File then Set as
Do you have some code such as
Set xlApp = New Excel.Application or Set xlApp = CreateObject("Excel.Application") -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MacroLearning" wrote in message ... I'm sorry - I don't know what that means... "Bob Phillips" wrote: Do you have ann. application object variable? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MacroLearning" wrote in message ... Thank you for posting an answer to my question. I am still getting a "debug" when I try to execute the macro. It won't set the opened file as xlbook. I get a 424 error code. I changed it to include your suggestion. Dim sFile sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then Set xlBook = Workbook.Open(sFile) <<<========== this is where it hangs up at Set xlSheet1 = xlBook.Worksheets("Headcount Detail") Set xlBook2 = Workbooks.Open( _ "W:\\Distribution File Cuts\Headcount by Bank Title - Quirk.xls") Set xlSheet2 = xlBook2.Worksheets("Headcount Detail") Set xlSheet7 = xlBook1.Worksheets("Headcount Summary") Set xlSheet8 = xlBook2.Worksheets("Headcount Summary") xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1") xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1") xlSheet2.Rows("40:47").Delete Any help would be greatly appreciated. "Bob Phillips" wrote: Dim sFile sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then Set xlBook = Workbook.Open(sFile) End If -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MacroLearning" wrote in message ... Hi all, I have a macro that works as I want. When I first started building I included the original filename to copy from. That file will change every month. So I want the user to open the file, when the file is opened, it will become "xlBook1". following is the code. I'm getting a debug at that point, but am not sure how to get past it. Any help will be very much appreciated! 'Book 1 is Headcount by Detail Report Dim xlBook1 As Excel.Workbook 'Book 2 is Quirk (all except SSGA) Dim xlBook2 As Excel.Workbook 'Sheet7 is HC Summary worksheet from HC Detail Report Dim xlSheet7 As Excel.Worksheet 'Sheet8 is HC Summary worksheet for Quirk Dim xlSheet8 As Excel.Worksheet 'Copy and Paste from Headcount Detail to Quirk Set xlBook1 = Application.GetOpenFilename <=====This is the problem here Set xlSheet1 = xlBook1.Worksheets("Headcount Detail") Set xlBook2 = Workbooks.Open( _ "C:\\Distribution File Cuts\Headcount by Title - Quirk.xls") Set xlSheet2 = xlBook2.Worksheets("Headcount Detail") Set xlSheet7 = xlBook1.Worksheets("Headcount Summary") Set xlSheet8 = xlBook2.Worksheets("Headcount Summary") xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1") xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1") xlSheet2.Rows("40:47").Delete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Excel Discussion (Misc queries) | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
Open a file do a macro ( made) and open next succesive file | Excel Programming | |||
Open File or Switch Between Windows if File is Open | Excel Programming | |||
Open File or Switch Between Windows if File is Open | Excel Programming |