Common Dialog 1

[valgore]

So, i found a lot of code that lets you select a file and drop the path into a text box, but i need to be able to import an excel spreadsheet. i found some code on the internet

Option Compare Database
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

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

Function LaunchCD(strform As Form) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.hwnd
    sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
      "JPEG Files (*.JPG)" & Chr(0) & "*.JPG" & Chr(0)
    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 = "Select a file using the Common Dialog DLL"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select a file using the Common Dialog DLL"
         Else
            LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function

this goes into a module and then

Private Sub Command0_Click()
    Me!Text1 = LaunchCD(Me)
End Sub

and that goes in a command click.
but this puts the path into a textbox. how do i make it import the selected file?

valgore

 

[HarleyQuinn]

Have a look into using DoCmd.TransferSpreadsheet with the acImport parameter. Also, thread705-760625 might be of some use to you.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[valgore]

ok, so i tried that and nothing happens. i feel bad that your helping me yet again . so the code i posted above, it puts the path name into a text field. is there a way to tell the docmd.transferspreadsheet function to pull that path out and import it?

Valgore

 

[PHV]

Use the value returned by your function instead of an hardcoded .xls name.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

You don't say what didn't work? Doesn't matter, no worries, I'm happy to help

But we can do it without using the textbox, how about:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "NewTable", LaunchCD(Me) ', True ' uncomment if has a header row

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[valgore]

I keep getting

Run-Time error 2391
Field 'F1' doesn't exist in destination table 'AmexCurrent.'

i think if i can get that fixed, we should be good.

Valgore

 

[HarleyQuinn]

If you add column headers to your excel sheet (that match the column headers in the table you're trying to import into and then use this it should work fine:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "AmexCurrent", LaunchCD(Me), True

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[valgore]

i did. the headers and exactly the same in the spreadsheet and the table. the only thing that is different is that i have a checkbox field on the table and not the spreadsheet

so, do all the field names in the table need to be in the spreadsheet? if that is so, can i import the spreadsheet into a different table and move what columns i want from that table to AmexCurrent?
Valgore.

 

[PHV]

can i import the spreadsheet into a different table and move what columns i want from that table to AmexCurrent?
You should follow that way as you'll check the imported data before filling the table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

ok cool. i head in that direction and if i come across any problems (I shouldn't... hopefully) ill let you know.

Thanks again!

Valgore

 

[valgore]

ok this is what i did. After the spreadsheet imports, i want only specific columns to move to AmexCurrent. this is what i tried

Private Sub Command0_Click()
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "RawImport", LaunchCD(Me) ', True, A:J
 Insert Into AmexCurrent
 Select [Cardholder Name], [Process Date], [Merchant Name/Location], Amount
 From RawImport WHERE Trim([RawImport] & '')<>''"

im getting an error on the From RawImport WHERE. im assuming its all syntax issue. any help?

Valgore

 

[pjm]

Insert Into...

isn't VBA.

Perhaps you meant:

docmd.runsql "Insert Into..."

or

currentdb.execute "Insert Into..."

pjm

 

[valgore]

Still not working..

DoCmd.RunSQL "Insert Into AmexCurrent"
 Select [Cardholder Name], [Process Date], [Merchant Name/Location], Amount
 From RawImport WHERE Trim([RawImport] & '')<>''"

i get a syntax error on the select row.

Valgore

 

[PHV]

DoCmd.RunSQL "Insert Into AmexCurrent " _
 & "Select [Cardholder Name], [Process Date], [Merchant Name/Location], Amount " _
 & "From RawImport WHERE Trim([RawImport] & '')<>''"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

wow. im still getting that error..
Field F1 doesn't exist in destination table 'RawImport'
ive tried removing the range in the code, ive tried changing true to false.. this is making me very frustrated....

 

[valgore]

ok. i fixed that part, but im getting
Run-time error 3103
Circular reference caused by alias 'cardholder name' in query defintions SELECT list

never seen this one before

Valgore

 

[bubba100]

I would suggest that you Don't try to stop it. Deal with it AFTER its in your table "RawImport".

 

[valgore]

ok so now i have 2 buttons. one that imports everything to RawImport and then another button to move only certain columsn from RawImport to AmexCurrent. i still get that error i posted

Valgore

 

[valgore]

ok. i changed the code around to this

Private Sub Command31_Click()
DoCmd.RunSQL "Insert Into AmexCurrent " _
 & "Select [RawImport!Cardholder Name!], [RawImport!Process Date!], [RawImport!Merchant Name/Location!], RawImport!Amount! "
 & "From RawImport WHERE Trim([RawImport] & '')<>''"
End Sub

and it got past the Select statement, but it says syntax error on the From line


Valgore

 

[pjm]

you need to use line continuation characters [red]_[/red]
if you are splitting a VBA statement [red]_[/red]
over multiple lines