Question on How to Grab an Excel file on the local hard drive

[Brad_512]

Hello, I was needing some help coming up with the code to search the local hard drive and grab a file and then import it into an Access table. I'm trying to do this on a form if possible. If you know of another way, that would work. Any help would be appreciated. Thanks.

 

[bonekrusher]

First Check out Ghudson's example on how to browse and find a file here:

http://www.access-programmers.co.uk/forums/showthread.php?t=97787

to import an excel file, you'll need to use Automation. Just do a search and there is plenty of threads on this.

Good luck

 

[Brad_512]

Here's where I'm at....

Thanks for your help! I found out how to locate a .csv file through the local directory. I also am able to show the file's location in a text box. Others examples are helpful, but still have some differences that are hard for me to edit into my current form. What I need to know is how to import the file from the code I already have:

Option Compare Database
'--------------------------------C------------------------------------------------------- 1
'[Declare find file API]
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
'[Define OPENFILENAME]
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type





'--------
'[Find it] 2
'--------
Private Sub Find_CSV_Click()
Beep
Beep
Beep
Beep
Beep
'MsgBox "2 THINGS! 1ST YOU MUST sort the csv by the ENTRY POINT FACILITY TYPE, Then by ENTRY POINT ZIP. Second make sure you make a copy of your original CSV file because this process will re-write it.", vbCritical + vbOKOnly, "!!WARNING!!"

MsgBox "****FIND THE .CSV FILE YOU WISH TO IMPORT****", vbCritical + vbOKOnly

Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Me.Hwnd
'OpenFile.hInstance = Me.hInstance
sFilter = "Text Files (*.prn ; *.txt; *.csv)"
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = "Find the correct .CSV File Please."
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "You (the user) pressed the Cancel Button, so no CSV file was grabbed"
Else
txtCSVFileToUse = Trim(OpenFile.lpstrFile)
End If

Exit_Find_CSV_Click:

Exit Sub

Err_btnFindCSV_Click:
MsgBox Err.Description
'MsgBox Err.Description & "btnFindCSV_Click"
Resume Exit_Find_CSV_Click

End Sub



Private Sub unbound_BeforeUpdate(Cancel As Integer)

End Sub

 

[bonekrusher]

try searching the forum. Also I bought this book to work with Automation. The link has many sample on importing data.

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764559044,descCd-download_code.html

 

[Brad_512]

Couldn't find a form that imported

I couldn't find anything from those samples that imported using a form. Do you know where I could find some sample code that illustrates how to import a file that's been pulled into a text box? I just need to know how a simple sample code thing to Import a .csv file would look like. When I click Import, I want it to be stored in a table. I just need to know the code and have example variables stated where I can just plug in my own variables into the code. I know I'm probably asking for too much, but I dont know enough to change the code to fit my own code already created.

 

[Brad_512]

Me.tbHidden.SetFocus?

What does this line do?

Me.tbHidden.SetFocus

It's where I'm receiving an error in the programming. I used what you had in a previous thread for importing a file, and edited it to fit my own. Just wondering what this line means?

 

[bonekrusher]

it places the focus on that control

try placing a " ' " in front of it and see what happens.

 

[Brad_512]

Can't seem to find an import example to fit mine

With the 3 or 4 different examples of imports I found, I can't seem to find one that's close enough to mine to where the modifications aren't difficult. When importing, what types of code need to be in the sub?

 

[bonekrusher]

samples are used to help you learn. Try to disect the code. It takes time, but thats how we all learned.

Good luck

 

[Brad_512]

The problem

The problem is I don't think any of these examples (which are only a handful that apply to importing) have the necessary code I can use to decipher what I need to use in my import sub command. I feel I'm getting pretty close, but like when it comes to field names, how do I import when the field names in my access database don't exactly match up with the field names in the .csv file I'm trying to import. I tried running a particular file, so I wrote out the exact filename I was importing. I got a run-time error stating that the field name in my .csv file is unrecognized. I'm assuming I have to somehow define the field names in the import command. Would I need to type out every field name in some form or fashion and if so, then how do I go about doing this? If so, how do I make it to where my field names in my .csv file will automatically import in the correct fields in my access table? For instance, my .csv file has the field name "PRO #". In my table, however, the field name for this is "K95_PRONUM". How do I match these up to where it will place the right data in the right fields (even though the similar field names are titled differently)?