Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Common Dialog 1

Status
Not open for further replies.
valgore

valgore

Technical User
Nov 12, 2008
180
US
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
Code: 
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
Code: 
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
 
Sort by date Sort by votes
ok. so i put the code all on one line and i still get the run-time error.
 
Upvote 0 Downvote
How about if you use this?
Code: 
Private Sub Command31_Click()
DoCmd.RunSQL "Insert Into AmexCurrent Select [Cardholder Name], [Process Date], [Merchant Name/Location], [Amount] From RawImport WHERE Trim([RawImport] & '')<>''"
End Sub
?

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.

 
Upvote 0 Downvote
Actually, looking at my code it's almost word for word what PHV posted on 27 Mar 09 11:23.

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.

 
Upvote 0 Downvote
yeah. ive tried that and i still get the
Run-time error 3103
Circular reference caused by alias 'Cardholder Name' in query definition's SELECT list
 
Upvote 0 Downvote
How about:
Code: 
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
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.

 
Upvote 0 Downvote
Run-time error 3125
RawImport!Cardholder Name is not a valid name. make sure that it does not include invalid characters or punctuation and that it is not too long.

At least it recognizes it. [smile]

Valgore
 
Upvote 0 Downvote
Hmm, weirdly, something like this works for me (after testing and generating the same errors as you:
Code: 
Private Sub Command31_Click()
DoCmd.RunSQL "Insert Into AmexCurrent Select [RawImport]![Cardholder Name] as [Cardholder Name], [RawImport]![Process Date], [RawImport]![Merchant Name/Location], [RawImport]![Amount] From RawImport WHERE Trim([RawImport] & '')<>''"
End Sub
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.

 
Upvote 0 Downvote
cool. it didnt work at first, but i just put as [] after each field. now its giving me a parameter popup for RawImport. no idea whats supposed to go there if anything

Almost there!

Valgore
 
Upvote 0 Downvote
Is RawImport a field in your RawInput table?

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.

 
Upvote 0 Downvote
no. RawImport is a table. but i just put Where trim([RawImport!Amount!]. that should be fine right? Amount is a random field in RawImport.

Valgore
 
Upvote 0 Downvote
Do you want to transfer only selected records from RawInput (i.e where a field isn't blank?) if so then use the WHERE statement with the associated criteria.

If you want all records from RawInput (but only the fields you specify in the SELECT) then don't use a WHERE clause at all.

Hope that 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.

 
Upvote 0 Downvote
yeah. i want all the records from those specific field and i want to delete everything else. i want to thank you again for your infinite wisdom HarleyQuinn and PHV. i think im officially ready for a live test.

Valgore
 
Upvote 0 Downvote
Glad to help, thanks for the star [smile]

Hope the live testing goes well.

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.

 
Upvote 0 Downvote
well.. like an idiot. i forgot to add code that saves the changes. i still havent gotten the app to work on other PC. but i can get the same app to work on my PC.. anyways.

How to i right code that says when anything changes automatically save.

Valgore
 
Upvote 0 Downvote
Saves the changes to what?

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.

 
Upvote 0 Downvote
so for instance, i make a run-time app and if i input some more data in a table, it wouldnt save. i dont have a save button/code.
 
Upvote 0 Downvote
so im trying to figure out what would be better. right now i have a startup form that pops up when the user opens the run-time app. If users go in and alter a form that is linked to a table, and close the form, will the changed data still be there? if i only have a save button on the startup form, will it save any changes that were made to the tables/forms/etc even if they are closed? My next question is with the DoCmd.Save function. how can i make it save the current form that is linked to the table automatically on close or if something changes?
Would it be something like

If [Form] closes, then DoCmd.Save [form] ??

Valgore
 
Upvote 0 Downvote
Bind the form to a table and set the form to 'Data Entry' = "Yes" ...if it is unbound you have to open a record set and write the data to the table ...
By the sounds of it you may find the former to be easiest

MaZeWorX


Remember amateurs built the ark - professionals built the Titanic

[flush]
 
Upvote 0 Downvote
cool. i will check that out.

Valgore
 
Upvote 0 Downvote
ok i tried that, and when Data Entry is on, and i filter the form, it adds a blank record and it cancels out my filter. any other suggestions?

Valgore
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

patriciaxxx
  • Locked
  • Question
Ken Getz Open File Dialog And Multiselect Large Number Files
Replies
14
Views
255
fdcusa
fdcusa
ICCIIT
  • Locked
  • Question
Open selected file using file dialog
Replies
4
Views
140
Andrzejek
Andrzejek
PortyAL
  • Locked
  • Question
Insert hyperlink dialog - current folder 2
Replies
5
Views
127
PortyAL
PortyAL
yamiusingvba
  • Locked
  • Question
Selecting multiple files from open dialog and splitting them into strings
Replies
4
Views
190
yamiusingvba
yamiusingvba
JoseMarques
  • Locked
  • Question
copy data from a recordset to a common box vba access 2007
Replies
15
Views
169
PHV
PHV

Part and Inventory Search

Sponsor

Back
Top