Import data from excel file

[SKV]

I want to import couple of excel files in my data. Below are the details of 1 of the excel file. I would like to have a simple VB code or some macro or query to import the data<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Excel File Name: File_1<o></o>
TAB Name: TAB_4 (there are multiple tabs in the file)<o></o>
Column Heading Start: Column B<o></o>
Column Heading End: Column AI<o></o>
Field Heading: Row 4<o></o>
Data Start: Row 5<o></o>
Data end: dynamic<o></o>
<o> </o>
The problem I am facing in importing the data is the data heading starts from row 4 and row 1 through 3 are filled with un-required information for my process<o></o>
<o> </o>
Can you please help me getting a code for this type of import. I need it asap to build some reports after getting this information.<o></o>
<o> </o>
Let me know if you need more details.<o></o>

 

[sumuwin]

Hi

untested but something along these lines should work - I've just set the range from B4 to AL50000 - consecutive blank rows should be ignored.

Sub XLimport()

Dim strPath As String
Dim strFile As String

strPath = "C:\"
strFile = "File_1.xls"

strFile = strPath & strFile

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "XLtable", _
                                        strFile, True, "Tab_4!B4:AL50000"

End Sub

atb
sumuwin

 

[Joe4]

Note there there is a Macro command for TransferSpreadsheet. This will show you all the arguments and give help on each one.

What I will often do is set up an example one using a Macro, save it, and then use the "Convert Macros to Visual Basic" utility to convert it to VBA code where I can modify it to suit my needs.

Note that with the TransferSpreasheet command, you can tell it whether there are field headers, and you have the option of importing using Named Ranges instead of static cell references. What I will often do is to create some VBA on the Excel side to dynamically name my range (the same thing every time), and then use that Named Range in my TransferSpreadsheet command, so the range can be dynamic.

 

[SKV]

how to see the VBA code for the Macros? I have access 2007 and notfinding the way to see the VBA code.

Please advice

Thanks
SKV

Note there there is a Macro command for TransferSpreadsheet. This will show you all the arguments and give help on each one.

What I will often do is set up an example one using a Macro, save it, and then use the "Convert Macros to Visual Basic" utility to convert it to VBA code where I can modify it to suit my needs.

Note that with the TransferSpreasheet command, you can tell it whether there are field headers, and you have the option of importing using Named Ranges instead of static cell references. What I will often do is to create some VBA on the Excel side to dynamically name my range (the same thing every time), and then use that Named Range in my TransferSpreadsheet command, so the range can be dynamic.

 

[Joe4]

Highlight the Macro you created in the Objects section, and then go to the Database Tool menu/ribbon and select the "Convert Macros to Visual Basic" found in the Macro box.

 

[SKV]

Code not working, please help

Hi

untested but something along these lines should work - I've just set the range from B4 to AL50000 - consecutive blank rows should be ignored.

Sub XLimport()
 
Dim strPath As String
Dim strFile As String
 
strPath = "C:\"
strFile = "File_1.xls"
 
strFile = strPath & strFile
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "XLtable", _
                                        strFile, True, "Tab_4!B4:AL50000"
 
End Sub

atb
sumuwin

 

[sumuwin]

What error do you get ?

The code works perfectly for me and creates a new table.

Have you checked the path, filename and tab name are correct? Change the following parts of the code as required

strPath = "C:\"

strFile = "File_1.xls"

"Tab_4!B4:AL50000"



sumuwin