Export Excel data and scheduled task

[joannele71]

Hi

I have the following macro to automatically export a range of excel data
into a txt file. If I run the macro manually, it works fine. But if I put
the macro to a scheduled task, the macro can't recognize the export name
(c:\joanne\test.txt~) and so a pop up window asks me for the export name.

Anything wrong with my scripts below? how to fix it?

Many thanks
joanne





Sub testEXPORT()

Workbooks.Open(Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros
Which:= _
xlAutoOpen

Workbooks("test.xls").Activate
Sheets("sheet1").Select
Application.SendKeys ("c:\joanne\test.txt~")
Range("A6").End(xlDown).Select
ActiveCell.Offset(0, 4).Select
anchor_cell2 = ActiveCell.Address
Range("A6", anchor_cell2).Select



Application.Run Macro:=Range( _
"[FLATFILE.XLA]FLATFILE!mcp01.FixedFieldExport")
Application.DisplayAlerts = True
End Sub

 

[Joel]

I can't tell from which workbooks you are trying to run the macros. I tried
modifying the code but probably got the workbooks wrong.

Sub testEXPORT()

set Flatfile = Workbooks.Open( _
Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros _
Which:=xlAutoOpen

with Workbooks("test.xls").Sheets("sheet1")
.Application.SendKeys ("c:\joanne\test.txt~")
set LastCell = .Range("A6").End(xlDown)
anchor_cell2 = Last.Offset(0, 4).Address
Range("A6", anchor_cell2).Select
.Application.Run Macro:=Range( _
"[FLATFILE.XLA]FLATFILE!mcp01.FixedFieldExport")
.Application.DisplayAlerts = True
end with
End Sub

 

[joannele71]

Hi Joel,

I am trying to run the macro on the test.xls file.

The first line of the macro (same as below) is to open the flat file in
which it shows the Export option in Excel.
Workbooks.Open(Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros
Which:= xlAutoOpen

I hope this helps.

Thanks
Joanne

I can't tell from which workbooks you are trying to run the macros. I tried
modifying the code but probably got the workbooks wrong.

Sub testEXPORT()

set Flatfile = Workbooks.Open( _
Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros _
Which:=xlAutoOpen

with Workbooks("test.xls").Sheets("sheet1")
.Application.SendKeys ("c:\joanne\test.txt~")
set LastCell = .Range("A6").End(xlDown)
anchor_cell2 = Last.Offset(0, 4).Address
Range("A6", anchor_cell2).Select
.Application.Run Macro:=Range( _
"[FLATFILE.XLA]FLATFILE!mcp01.FixedFieldExport")
.Application.DisplayAlerts = True
end with
End Sub

Hi

I have the following macro to automatically export a range of excel data
into a txt file. If I run the macro manually, it works fine. But if I put
the macro to a scheduled task, the macro can't recognize the export name
(c:\joanne\test.txt~) and so a pop up window asks me for the export name.

Anything wrong with my scripts below? how to fix it?

Many thanks
joanne





Sub testEXPORT()

Workbooks.Open(Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros
Which:= _
xlAutoOpen

Workbooks("test.xls").Activate
Sheets("sheet1").Select
Application.SendKeys ("c:\joanne\test.txt~")
Range("A6").End(xlDown).Select
ActiveCell.Offset(0, 4).Select
anchor_cell2 = ActiveCell.Address
Range("A6", anchor_cell2).Select



Application.Run Macro:=Range( _
"[FLATFILE.XLA]FLATFILE!mcp01.FixedFieldExport")
Application.DisplayAlerts = True
End Sub

 

[Joel]

when you work with more than one open workbook you should speciufically
reference each workbook in all statement to make sure the right workbook is
being addressed. I made one small change below in the new code. I think you
need to add simlar changes to the rest of the code. I also thing you need to
activate the workbook (activating is probably automatrtic because the newly
opened workbook automatically becomes the active workbook).

testEXPORT()

set Flatfile = Workbooks.Open( _
Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros _
Which:=xlAutoOpen

with Workbooks("test.xls").Sheets("sheet1")
.activate 'added to activate workbook
Flatfile.Application.SendKeys ("c:\joanne\test.txt~") 'added Flatfile
set LastCell = .Range("A6").End(xlDown)
anchor_cell2 = Last.Offset(0, 4).Address
Range("A6", anchor_cell2).Select
.Application.Run Macro:=Range( _
"[FLATFILE.XLA]FLATFILE!mcp01.FixedFieldExport")
.Application.DisplayAlerts = True
end with
End Sub

Hi Joel,

I am trying to run the macro on the test.xls file.

The first line of the macro (same as below) is to open the flat file in
which it shows the Export option in Excel.
Workbooks.Open(Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros
Which:= xlAutoOpen

I hope this helps.

Thanks
Joanne

I can't tell from which workbooks you are trying to run the macros. I tried
modifying the code but probably got the workbooks wrong.

Sub testEXPORT()

set Flatfile = Workbooks.Open( _
Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros _
Which:=xlAutoOpen

with Workbooks("test.xls").Sheets("sheet1")
.Application.SendKeys ("c:\joanne\test.txt~")
set LastCell = .Range("A6").End(xlDown)
anchor_cell2 = Last.Offset(0, 4).Address
Range("A6", anchor_cell2).Select
.Application.Run Macro:=Range( _
"[FLATFILE.XLA]FLATFILE!mcp01.FixedFieldExport")
.Application.DisplayAlerts = True
end with
End Sub

Hi

I have the following macro to automatically export a range of excel data
into a txt file. If I run the macro manually, it works fine. But if I put
the macro to a scheduled task, the macro can't recognize the export name
(c:\joanne\test.txt~) and so a pop up window asks me for the export name.

Anything wrong with my scripts below? how to fix it?

Many thanks
joanne





Sub testEXPORT()

Workbooks.Open(Filename:="c:\joanne\reference\FLATFILE.XLA").RunAutoMacros
Which:= _
xlAutoOpen

Workbooks("test.xls").Activate
Sheets("sheet1").Select
Application.SendKeys ("c:\joanne\test.txt~")
Range("A6").End(xlDown).Select
ActiveCell.Offset(0, 4).Select
anchor_cell2 = ActiveCell.Address
Range("A6", anchor_cell2).Select



Application.Run Macro:=Range( _
"[FLATFILE.XLA]FLATFILE!mcp01.FixedFieldExport")
Application.DisplayAlerts = True
End Sub