import Excel into Access using VBA

[Arttrivedi]

Ok I nave this new req.

I am trying to import excle file into access table using VBA code. Excel file DOES NOT have any column heading.

I cannot use "DoCmd.TransferSpreadsheet " because it will give me error.

Any suggestions ??????

 

[bboffin]

You can do it the hard way by creating an Excel.Application object then opening the spreadsheet and processing the cells yourself. It's a bit fiddly but it works pretty well.

See the Excel VBA Object model for more details.

 

[Arttrivedi]

I guess thats probably the only way.

Do you know how to create an Excel Application Object to open up the excle file?

 

[LNBruno]

I've been working with the DoCmd.TransferSpreadsheet with Excel myself the last couple of days and have finally gotten the syntax so it works. What error are you getting? I've probably hit most of them in the last 48 hours...

 

[Arttrivedi]

To: LNBruno
The Error is
"Field 'F1' doesn't exist in destination table 'tblChild.'"

once again this is due to NO COLUMN HEADING.
if I set the column headings in excle file to match my table in access, it works just great.

 

[LNBruno]

If your statement includes 'True' as in:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, "c:\mytestfile.xls", True

replace 'True' with 'False':

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, "c:\mytestfile.xls", False

The 'True' means the spreadsheet has field names.

 

[Arttrivedi]

I tried that too, making
"DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, "c:\mytestfile.xls", False
"
False

but same error.

When you try to import using File > Import > FilName > Uncheck "First Row Contains Row Headings"

you will have to import into New Table, you cannot imort into existing table..

 

[LNBruno]

Don't know if this is an option, but using the TransferText seems to work pretty well. Note that I'm using the acImportDelim parameter, along with an Import Specification. You'd have to change the params to match your needs.

    '--create Excel object:
    Set objExcelApp = New Excel.Application
    
    '--open the ACT! "table" in Excel:
    objExcelApp.Workbooks.Open (sFromPath & sFromFile)
    Set objWB = objExcelApp.Workbooks(1)
    
    '--make sure no dialogs pop up,
    '--run in background as a process,
    '--save the worksheet, then close Excel:
    With objExcelApp
        .DisplayAlerts = False
        .Workbooks(1).SaveAs sToPath & sTable & ".txt", FileFormat:=xlText
        .Workbooks(1).Close
        .DisplayAlerts = True
    End With
    Set objExcelApp = Nothing
    Set objWB = Nothing
    
    '--make sure no dialogs pop up:
    DoCmd.SetWarnings False

    '--clear the table:
    DoCmd.RunSQL "DELETE FROM " & sTable

    '--import the data:
    DoCmd.TransferText acImportDelim, sTable & " Import Specification", sTable, sToPath & sTable & ".txt", True
    DoCmd.SetWarnings True

    '--kill the temp file:
    Kill sToPath & sTable & ".txt"

 

[bboffin]

If you end up using Excel directly add a reference to the appropriate version of Excel. (Tools|References with a module window open)

Then try:

Dim oExcel as New Excel.Application
Dim oSheet as Excel.Worksheet
oExcel.WorkBooks.Open "xxxx.xls"
Set oSheet = oExcel.ActiveWorkbook.Worksheets("SheetName&quot

You can then use the Cells property of the Worksheet (oSheet) to access the cells you need.

 

[Arttrivedi]

Thank you both for your valuable input. The Code below seems to work:

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Dim db As Database, rec As Recordset
Dim wb As Workbook
Dim x As String, y As String, z As String
Dim A As String, B As String, C As String

Set db = CurrentDb
Set rec = db.OpenRecordset("tblChild&quot
Set wb = Workbooks.Open("C:\Foldername\Filename.xls", True, True)
' open the source workbook, read only
Do Until wb.Worksheets("Sheet1&quot.Range(A).Formula = ""
x = wb.Worksheets("Sheet1&quot.Range(A).Formula
y = wb.Worksheets("Sheet1&quot.Range(B).Formula
z = wb.Worksheets("Sheet1&quot.Range(C).Formula
rec.AddNew
rec.Fields("Field1&quot = x
rec.Fields("Field2&quot = y
rec.Fields("Field3&quot = z
rec.Update

i = i + 1
A = "A" & i
B = "B" & i
C = "C" & i
Loop
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
rec.Close
db.Close
End Sub

 

[Tomadams]

I am sorry to but in after you get this thing working, but, does your table that you are importing to already exist???

 

[Arttrivedi]

Tables already exist in Acces Database. Based on those table and field names I am creating a Cross Tab Query. For this reason I cannot change the name of the fields in my table.