Mike Rudolph
asked on
Access VB Script Save As Error
Hello Experts,
For some reason I get an error message (screenshot1) when I try to import .txt data into my Access table. This script worked when I was using MS Access 2003. I am using Access 2007 so I just switch the extension from .xls to .xlsx thinking that was all I needed to do but I was wrong. The VB code just stops at "objWB.SaveAs "C:\LSS\import.xlsx", 43"
Any suggestions?
Thanks!
Mike
For some reason I get an error message (screenshot1) when I try to import .txt data into my Access table. This script worked when I was using MS Access 2003. I am using Access 2007 so I just switch the extension from .xls to .xlsx thinking that was all I needed to do but I was wrong. The VB code just stops at "objWB.SaveAs "C:\LSS\import.xlsx", 43"
Any suggestions?
Thanks!
Mike
Private Sub cmdImport_Click()
Dim objXL As Object
Dim objWB As Object
Dim strSQL As String
Dim Response As Integer
Response = MsgBox("Import Student Data? 'Yes' or 'No'.", _
vbYesNo, _
"Import Student Data")
If Response = vbYes Then
If (Len(Dir("C:\LSS\Import.txt")) = 0) Then
MsgBox "The Import File Missing [ C:\LSS\import.txt ]", vbExclamation, " :: No file to Import ::"
Else
If (Len(Dir("C:\LSS\import.csv")) > 0) Then Kill "C:\LSS\import.csv"
If (Len(Dir("C:\LSS\import.xlsx")) > 0) Then Kill "C:\LSS\import.xlsx"
FileSystem.FileCopy "C:\LSS\import.txt", "C:\LSS\import.csv"
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Open("C:\LSS\import.csv")
objWB.SaveAs "C:\LSS\import.xlsx", 43
objWB.Close
Set objWB = Nothing
objXL.Quit
Set objXL = Nothing
strSQL = "DELETE * FROM tblStudent"
CurrentDb.Execute strSQL
strSQL = "INSERT INTO tblStudent (tblStdD, tblStdActive, tblStdLname, tblStdFname, tblStdMI, tblStdDOB, tblStdGender, tblStdGrade, tblStdEntryDate, tblStdTier, tblStdServiceModel, tblStdSchoolID) " & _
"SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12 " & _
"FROM [Import$] IN 'C:\LSS\import.xlsx'[Excel 5.0;HDR=NO;IMEX=1;DATABASE=C:\LSS\import.xlsx] " & _
"WHERE (Not (F1) Is Null);"
CurrentDb.Execute strSQL
If (Len(Dir("C:\LSS\import.csv")) > 0) Then Kill "C:\LSS\import.csv"
If (Len(Dir("C:\LSS\import.xlsx")) > 0) Then Kill "C:\LSS\import.xlsx"
MsgBox "Congratulations! Import Process Complete!", vbInformation, " File Import"
End If
Else
DoCmd.CancelEvent
End If
End Sub
screenshot2.png
Access 2007 coding went from vba to the .net framework. You will probably find that there is a different syntax for the .saveas function now. Ill see if I can find it in a couple of minutes on google.
Shogun,
You may check this out:
In the code you posted you have this line:
objWB.SaveAs "C:\LSS\import.xlsx", 43
But in the screenshot error, you got this:
objWB.SaveAs "C:\LSS\import.xls", 43
Try to change .xls to xlsx
Ed
You may check this out:
In the code you posted you have this line:
objWB.SaveAs "C:\LSS\import.xlsx", 43
But in the screenshot error, you got this:
objWB.SaveAs "C:\LSS\import.xls", 43
Try to change .xls to xlsx
Ed
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep! That was it. Thanks!
43 is saving as Excel9795 format. I reckon its cos your changing to 2007 file suffix it is complaining. I could be wrong but that is the educated guess I am taking.
So why do you want to change the suffix? A2007 is backwards compatible so it can handle just .xls files
So would just doing this (note the suffix)
objWB.SaveAs "C:\LSS\import.xls", 43
work?
or try specifying the default workbook file type
objWB.SaveAs "C:\LSS\import.xlsx", 51
So why do you want to change the suffix? A2007 is backwards compatible so it can handle just .xls files
So would just doing this (note the suffix)
objWB.SaveAs "C:\LSS\import.xls", 43
work?
or try specifying the default workbook file type
objWB.SaveAs "C:\LSS\import.xlsx", 51
oh well. too slow in typing
Note, if you are wanting to convert csv to xls then fileformat=6 still leaves it as csv, not in xls format but if it suits your needs then it's fine.
Note, if you are wanting to convert csv to xls then fileformat=6 still leaves it as csv, not in xls format but if it suits your needs then it's fine.
From what I gathered, the fileformat=6 was indicating the input file type. It should save as an xlsx file OK.
I based what I said on the original code, opening csv then saving as excel95-97 format.
Saving as format 6 still works, since it is a csv, excel knows what to do with it, but I do not think it is in proper excel format, it is still in csv format
fileformat 6 is save as csv.
shogun hasn't responded so I take it he/she is not bothered with how it is saved, so as long as it works.
Saving as format 6 still works, since it is a csv, excel knows what to do with it, but I do not think it is in proper excel format, it is still in csv format
fileformat 6 is save as csv.
shogun hasn't responded so I take it he/she is not bothered with how it is saved, so as long as it works.