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 screenshot1
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

Open in new window

screenshot2.png
shogun5Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
infolurkConnect With a Mentor Commented:
It looks like .net requires fileformat to be explicitly described.

Try updating the line to
objWB.SaveAs "C:\LSS\import.xlsx", fileformat:=6

Cheers
0
 
infolurkCommented:
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.
0
 
MINDSUPERBCommented:
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

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
shogun5Author Commented:
Yep! That was it. Thanks!
0
 
rockiroadsCommented:
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




0
 
rockiroadsCommented:
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.
0
 
infolurkCommented:
From what I gathered, the fileformat=6 was indicating the input file type. It should save as an xlsx file OK.
0
 
rockiroadsCommented:
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.
0
All Courses

From novice to tech pro — start learning today.