Solved

Access VB Script Save As Error

Posted on 2010-11-14
8
818 Views
Last Modified: 2012-05-10
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
Comment
Question by:shogun5
8 Comments
 
LVL 8

Expert Comment

by:infolurk
ID: 34133795
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
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34133806
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
 
LVL 8

Accepted Solution

by:
infolurk earned 500 total points
ID: 34133822
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Closing Comment

by:shogun5
ID: 34133853
Yep! That was it. Thanks!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34133873
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 34133878
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
 
LVL 8

Expert Comment

by:infolurk
ID: 34134116
From what I gathered, the fileformat=6 was indicating the input file type. It should save as an xlsx file OK.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34142438
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question