MrExcel Publishing
Your One Stop for Excel Tips & Solutions

importing data on workbook_open


Posted by carter on October 31, 2001 5:36 PM

Hi, I've got a procedure that imports data from a text file. It works fine as a macro, but I want it to execute whenever the workbook is opened. I tried just pasting the code into the workbook_open event, but this isn't working. Any suggestions?


Posted by Geoffrey Hurst on November 01, 2001 1:49 AM


Let's look at your code and tell us what you did to paste it into the workbook_open event.

Posted by carter on November 01, 2001 7:56 AM

Thanks.

Okay, here's the code. It's really just taken from a macro I recorded.

Private Sub Workbook_Open()
'
' importOrders Macro
' Macro recorded 10/31/01 by Jessica Abel
'
Sheets("Orders").Select
Range("A1").Select
Workbooks.OpenText FileName:="E:\tarotorders\Orders.txt", Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _
29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1))
Cells.Select
Selection.Copy
Windows("PackingSlip.xls").Activate
ActiveSheet.Paste
Windows("Orders.txt").Activate
ActiveWindow.Close
Sheets("Items").Select
Range("A69").Select
Workbooks.OpenText FileName:="E:\tarotorders\Items.txt", Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Select
Selection.Copy
Windows("PackingSlip.xls").Activate
Range("A1").Select
Sheets("Items").Select
ActiveSheet.Paste
Windows("Items.txt").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="E:\tarotorders\Products.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))
Cells.Select
Selection.Copy
Windows("PackingSlip.xls").Activate
Sheets("Products").Select
Range("A1").Select
ActiveSheet.Paste
Windows("Products.txt").Activate
ActiveWindow.Close
End Sub

Posted by Rudi Kroll on November 01, 2001 1:36 PM

Do you have it in the correct module?
It needs to be in the ThisWorkbook code module.

importOrders Macro Macro recorded 10/31/01 by Jessica Abel