What I want to do is Open the XLS file, Delete Row 1 change the font and then save as a simple txt file. Using acFormatTXT seems to skip a few rows. Regardless, my intent is to do this from within A2K2.
Is it possible? If no one knows, can someone point me in the correct direction?
it is possible. you can either write the macro in excel and then call it from access like this
Dim obExcel As Object
'sets the Macro spreadsheet as the object
Set obExcel = GetObject(c:\Macro.xls")
'runs the excel format to tidy up download
.DisplayAlerts = False
.Windows("Macro.xls").Visible = True
.Run "'Macro.xls'!GNSPER08" 'in this case GNSPER08 is the function name
Set obExcel = Nothing
or you can reference the Excel Objects dll from access by opening a module and going to Tools | References and use the soemthing like the following
Dim appExcel As New Excel.Application
Dim wkbExcel As Excel.Workbook
Dim wksExcel As Excel.Worksheet
'hides the instance of excel
appExcel.Visible = False
'creates new workbook object
Set wkbExcel = appExcel.Workbooks.Add
'creates new worksheet
Set wksExcel = wkbExcel.Worksheets.Add
'names the new worksheet
wksExcel.Name = "RawData"
'initialises with loop for the worksheet object
'adds something to the first cell
.Range("A1") = "this is a test"
'saves to temp folder
'closes excel instance
Set appExcel = Nothing
Set wkbExcel = Nothing
Set wksExcel = Nothing
in this case anything in the with loop is excel VBA.
Thank you... I used some of your code and some other. But... I have a ss which runs a macro at open. Opens another ss, formats, saves as txt and closes. Works just fine when opened from within Excel. When I run it from within Access, it doesn't run the macro. Access vba as follows:
try deleting the auto open code and put the formatfile function in a module then use this code in access to run the macro. i have attached a zip with a sample in it
theres just one form with the code on a button click event. i added 97 2000 & 2002 versions. it worked in all three
Thanks, I got it working with your code exactly. The first sample. I am not an excel expert and perhaps that was my problem. Only problem left as follows:
ss#1 is the one with the macro/vba which opens ss#2 created from A2K2 report. Works just fine. A2K2 opens ss#1, formats ss#2 properly and then closes ss#2. Only problem is that excel asks if I want to save ss#1 changes. Is there a way to turn this off (of course just for ss#1)?