By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,877 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Browse Excel File & Then Import In Access

P: 13
I have a VB code that will allow me to browse for a Excel file. After selecting that file, I want it to imported into Access. Following is the code for browsing a Excel file:

Private Sub Command0_Click()

Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:="Choose an Excel file...", _
Flags:=ahtOFN_HIDEREADONLY)

If Len(strInputFileName) > 0 Then
' Do something with the selected file
Else
'No file chosen, or user canceled
End If

End Sub

Can someone help me with how I can import that selected file.

Thanks.
May 18 '07 #1
Share this Question
Share on Google+
4 Replies


JConsulting
Expert 100+
P: 603
I have a VB code that will allow me to browse for a Excel file. After selecting that file, I want it to imported into Access. Following is the code for browsing a Excel file:

Private Sub Command0_Click()

Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:="Choose an Excel file...", _
Flags:=ahtOFN_HIDEREADONLY)

If Len(strInputFileName) > 0 Then
' Do something with the selected file
Else
'No file chosen, or user canceled
End If

End Sub

Can someone help me with how I can import that selected file.

Thanks.

A simple transfer spreadsheet command will do it

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, 8, "YourTable", strInputFileName, True, "!SheetName"
  2.  
J
May 19 '07 #2

P: 4
hi all,
i too need to browes for a file and import it but the above code doesn't work with me, i get an error of "sub of function not defined" for the command "ahtAddFilterItem" !!!

please help, i'm really new to VB and can't understand what's wrong
May 20 '07 #3

JConsulting
Expert 100+
P: 603
hi all,
i too need to browes for a file and import it but the above code doesn't work with me, i get an error of "sub of function not defined" for the command "ahtAddFilterItem" !!!

please help, i'm really new to VB and can't understand what's wrong

Hi Nashwa,
Generally you would open a new question of your own...and if you want, you can point to this one to say you need something similar.
J
May 20 '07 #4

P: 6
A simple transfer spreadsheet command will do it

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, 8, "YourTable", strInputFileName, True, "!SheetName"
  2.  
J
Thanks JConsulting! I am trying to use the code above as well (allbeit I am trying to create temporary links to delimited text files) so I have modified a little as below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. Dim strFilter As String
  4. Dim strInputFileName As String
  5.  
  6. strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)")
  7. strInputFileName = ahtCommonFileOpenSave( _
  8. Filter:=strFilter, _
  9. OpenFile:=True, _
  10. DialogTitle:="Choose a text file...", _
  11. Flags:=ahtOFN_HIDEREADONLY)
  12.  
  13. If Len(strInputFileName) > 0 Then
  14. ' Do something with the selected file
  15. Else
  16. 'No file chosen, or user canceled
  17. End If
  18.  
  19.     DoCmd.TransferText acLinkDelim, "SICK_RAW_DATA_LINK", "lnkRAW_SICK_FILE", strInputFileName
  20.  
  21. End Sub
  22.  
However, I get the "ahtAddFilterItem" hilighted and the error 'sub or function not defined." Is this a function that I can get from somewhere? Unfortunately, I know enough VB to get myself into trouble, but not enough to truly develop... Any help is appreciated.
Sep 20 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.