Import data from excel file

SKV

Active Member
Joined
Jan 7, 2009
Messages
257
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Excel File Name: File_1<o:p></o:p>
TAB Name: TAB_4 (there are multiple tabs in the file)<o:p></o:p>
Column Heading Start: Column B<o:p></o:p>
Column Heading End: Column AI<o:p></o:p>
Field Heading: Row 4<o:p></o:p>
Data Start: Row 5<o:p></o:p>
Data end: dynamic<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
Let me know if you need more details.<o:p></o:p>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.


Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.


Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top