Mitch Swetsky
asked on
Open CSV, modify and save as xls from Access
I have a CSV that I need to import to Access. The file needs to have some columns re-formatted.
I am getting message "cant access file..." I think because it is read only.
Code from Module follows:
Dim ExcelApp
Set ExcelApp = CreateObject("Excel.Applic ation")
ExcelApp.Workbooks.Open "C:\Temp\Report.csv"
ExcelApp.DisplayAlerts = False
'Next command Errors with "Cannot access Report.xls"
ExcelApp.ActiveWorkbook.Sa veAs FileName:="C:\Temp\Report. xls"
ExcelApp.Visible = False
ExcelApp.ActiveWorkbook.Cl ose True
ExcelApp.Workbooks.Open "C:\Temp\Report.xls"
ExcelApp.Visible = True
Workbooks("Report.xls").Sh eets(1).Ac tivate
Columns("G:H").Select
Selection.NumberFormat = "@"
Columns("I:K").Select
Selection.NumberFormat = "#,##0.00"
ExcelApp.ActiveWorkbook.Cl ose False
' More files follow Here.............
I am getting message "cant access file..." I think because it is read only.
Code from Module follows:
Dim ExcelApp
Set ExcelApp = CreateObject("Excel.Applic
ExcelApp.Workbooks.Open "C:\Temp\Report.csv"
ExcelApp.DisplayAlerts = False
'Next command Errors with "Cannot access Report.xls"
ExcelApp.ActiveWorkbook.Sa
ExcelApp.Visible = False
ExcelApp.ActiveWorkbook.Cl
ExcelApp.Workbooks.Open "C:\Temp\Report.xls"
ExcelApp.Visible = True
Workbooks("Report.xls").Sh
Columns("G:H").Select
Selection.NumberFormat = "@"
Columns("I:K").Select
Selection.NumberFormat = "#,##0.00"
ExcelApp.ActiveWorkbook.Cl
' More files follow Here.............
ASKER
Thank you for your reply Rey,
The line turns red and I get a compile error " Expected: named parameter"
The line turns red and I get a compile error " Expected: named parameter"
ASKER
I am using Office 2007-2010
When I change the format to 51 I can step through but get the cant access message
When I change the format to 51 I can step through but get the cant access message
oops, try this, missing ":"
ExcelApp.ActiveWorkbook.Sa veAs FileName:="C:\Temp\Report. xls", fileformat:=56
ExcelApp.ActiveWorkbook.Sa
ASKER
I have been going in these circles. lol
I get the cant access the file message and when I made the Excel visible in previous tries, the doc was read only.
I get the cant access the file message and when I made the Excel visible in previous tries, the doc was read only.
which line is raising the message?
ASKER
ExcelApp.ActiveWorkbook.Sa veAs FileName:="C:\Temp\Report. xls", fileformat:=56
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I deleted the file and checked task manager., there were many Excel applications running.
I ended those processes and ran the code without error.
Thank you,
Mitch
But my code is not re-formatting the columns.
I ended those processes and ran the code without error.
Thank you,
Mitch
But my code is not re-formatting the columns.
ASKER
Thank you for the error removal.
ASKER
Can you help me format the columns correctly?
try adding a line to save the workbook after the formatting
ExcelApp.ActiveWorkbook.Sa
with
ExcelApp.ActiveWorkbook.Sa