Results 1 to 9 of 9
  1. #1
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59

    Open / Save as Excel File and specifiy format

    I have a database that I am trying to import an excel file into. The excel file is coming from a Third Party software and is not importing. I get errors that it is not in a recognizable format, although the file HAS AN .xls extensnion. I open the file in Excel and then go to “Save As” and the Format is “Web Page (*.htm, *.html)”….



    If I change the format to .xls and save Access Accepts the File no problem.
    Yes I can make my users do this step but I am looking to automate this. They already have to create the xls file in a 3rd party software. I want to eliminate the Excel save and handle it from Access.

    I can use the code below to open Excel and save the file but it saves as the same format. After I run the code open it again with Excel and do a Save As and again it shows the format of “Web Page (*.htm, *.html)”

    I have seen examples like this: although it does not like the FileFormat:=xlNormal

    Code:
    wbk.SaveAs "F:\NEW_DATA_AFTER_SAVE_OFF\ENGINEERING_MAILING_LAB ELS\Mailing_Labels2.xls", FileFormat:=xlNormal
     
    


    Does anyone out there know how I can specify the format to save as an xls file and not a web page

    Thanks


    Code:
    Code:
    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
     
    Dim wbk As Object
    Set wbk = xl.Workbooks.Open("F:\NEW_DATA_AFTER_SAVE_OFF\ENGINEERING_MAILING_LAB  ELS\Mailing_Labels.xls")
     
    Dim wsht As Object
    Set wsht = wbk.worksheets("Mailing_Labels")
     
    Dim strFileName As String
    Dim lastRow As Integer
     
    With xl
    .Visible = True
    .UserControl = True
    xl.DisplayAlerts = False
    wbk.SaveAs "F:\NEW_DATA_AFTER_SAVE_OFF\ENGINEERING_MAILING_LAB  ELS\Mailing_Labels2.xls"
    wbk.Close
    End With
     
    Set xl = Nothing

  2. #2
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Oh yea I am using Access 2007

  3. #3
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Anyone have any ideas? Any help is greatly appreciated...

    THanks

  4. #4
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Anyone every do this?

  5. #5
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Guess not....thanks anyway...

  6. #6
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Try to Save it as xlCSV csv file format instead.
    The file name extension can be .xls.

  7. #7
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    If I open the excel file and save as an xlCSV

    Now I need to import to Access

    I am trying this but getting errors:

    Unparsable Record

    Any thoughts as to what that is?

    Could it be this: acSpreadsheetTypeExcel9 in the code below?

    Code:
     
            vrtSelectedItem = "F:\NEW_DATA_AFTER_SAVE_OFF\ENGINEERING_MAILING_LABELS\Mailing_Labels2.xlCSV"
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Mailing_Labels", vrtSelectedItem, False

  8. #8
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Maybe because some of the field values start with a number than in the second record it starts with a letter.

    Is the import thinking its Numeric and then assign numeric to the field, then errors because it is expeting a number na dit starts with a letter?

    That make any sense?

    Can I specify field by field what type I want it? (text, numeric etc)

    Thanks

  9. #9
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    My problem is that the xls file that I am trying to use is actually in WEb format (one of the options when you save from Excel) (it has an .xls extension but if you open it and click save as it defualts to the web format as a save type).

    I cannot import this into Access, it gives me unrecognizable format errors.

    I can bypass this error if I manually open the excel file and click save as .xls, then imports fine.

    I am trying to duplicate the work from within Access, hense open excel file in Excel, save it as an Excel file and then run the import.

    I cant figure this out....Any way I can paste the Excel file to show you what I mean....

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Why 2 users cant open an access file directly???
    By finnegan bell in forum Access
    Replies: 6
    Last Post: 03-22-2009, 09:06 PM
  2. Can not open File already in use.
    By Wrangler in forum Access
    Replies: 1
    Last Post: 03-04-2009, 09:50 PM
  3. Issues with dating when importing excel file
    By Lainie in forum Import/Export Data
    Replies: 0
    Last Post: 01-22-2009, 10:50 AM
  4. cmd to print/view an MS-Excel file
    By gkast in forum Forms
    Replies: 0
    Last Post: 11-23-2008, 07:39 PM
  5. Open Excel from within Access?
    By kjw in forum Access
    Replies: 2
    Last Post: 04-08-2008, 07:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums