Search results

bit of a noob here, I've got an import function to import different spreadsheets to access, but the field names are slightly different all the time from sheet to sheet. so what's the best way to get my data into a main table appending to the same columns that already exist in the table? at the...

i worked it out, if I close excel at the end of the command0click it retains the filename variable in the public even though excel is closed, and allows import of it :)

nope, so weird, it worked yesterday and all of a sudden it's not, the new code isn't helping as it's just not getting that far

that's the whole lot. I came to the realisation that it needs to kill excel processes this morning after the restart, I haven't put that in yet though, might try and get it working first. Private strPathAndFile As String Option Compare Database Private Sub Combo4_AfterUpdate() Dim MyRange As...

its an asset database, pretty much like a library catalogue, so to keep a history I was moving the record to the history table, and then putting the new record in the main table. If there's a better way of doing it I'd be interested to hear suggestions :)

I had a heap of EXCEL processes open so I restarted and tried again, same result. it's not getting anything in the immediate window, I'm guessing because it's not getting to the debug.print. the error is: Run-time error '3011': The Microsoft Access database engine could not fine the object...

for some reason that's actually stopped working :S the message box opens properly with the right file name and myrange is correct and then it says it can't find it and errors out

worked it out :) Private Sub Combo4_AfterUpdate() Dim MyRange As String MyRange = Combo4.Text MsgBox "" + MyRange + "" + strPathAndFile DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True End Sub Thank you so much for your help it's very much appreciated

yeah that worked, and I totally agree about the naming conventions. This whole thing started as a test to see what I could do with it, and what would be possible, and it's looking like anything is possible with the more I learn. The issue is now that Private Sub Combo4_AfterUpdate() Dim...

ok in the update for the combo box I've got: Private Sub Combo4_AfterUpdate() Dim MyRange As String MyRange = Combo4.Text MsgBox "" + strPathAndFile + MyRange 'DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True, "MyRange" which outputs the range as text, that's fine for...

hey, what am I doing wrong here? so far it lets the user select a file, adds the worksheets to a combobox now on the afterupdate() in the combo box i want to docmd.transfer spreadsheet based on the selection in the combobox. here's the code so far: Private strPathAndFile As String ' this is...

oh wow, I've been staring at this too long, somewhere along the way I deleted: strPathAndFile = fd.SelectedItems(1)

for some reason everything has stopped working now, so I took it back a few steps to here: and it's not getting to the msgbox with the strPathAndFile, all I did was add the global declarations and then that still failed :S Option Compare Database Private Sub Command0_Click() Dim fd As...

that's so awesome! I had no idea I could do that :D this is all I have in there" Private Sub Combo4_AfterUpdate() DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True End Sub

well, I want access to import the worksheet based on what the user selects so if I put DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True in combo4_Afterupdate() it says Runtime error 2522 the action or method required a File Name argument. That's what I mean when I say...

is that supposed to go in the combobox afterupdate()?

the false warnings are there to turn off the append queries saying you're about to append blah blah so the question now is, if I've loaded the worksheet names into that combo box how do i import that spreadsheet based on which value\worksheet the user selects. because putting...

yep that worked. here's the code for anyone interested With fd .AllowMultiSelect = False .Filters.Add "Excel Files", "*.xlsx", 1 .Title = "locate files" .InitialFileName = strsearchpath DoCmd.SetWarnings False If .Show = True Then For Each vrtSelectedItem In...

you're my new hero. I haven't tried it yet but I think that will work. I was just mapping it out and really I need to store that filename somewhere for later on, because when the combobox worksheet is selected i want to import it and run a bunch of queries. is that possible?

so I'm trying to use filedialog to open a file then pass that filename to dim myxlworkbook as excel.application so that it populates a combo box of the sheetnames which ill then take that selection of sheetnames for docmd.transferspreadsheet import. anyway I'm getting runtime error 91 Object...