Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

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
0
shogun5
Asked:
shogun5
1 Solution
 
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
 
infolurkCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now