Add file name to table when importing via spreadsheet (1 Viewer)

archer69

New member
Joined
Jan 17, 2017
Messages
7
Hi all,

I'm new to the form and very new (yesterday!) to VBA.

I have a number of spreadsheets, all formatted the same which I need to import into a single table in Access. Not quite sure how I managed it but I have a module that's now part of a query that does the job I want.

However I forgot that I needed to import the filename as the first column of data into the table (identifies a supplier)

I've found several threads but with NO VBA knowledge I'm really struggling to understand how to incorporate it into my code, can anyone help?

My code so far is:

Function Import_Excel()

Dim myfile
Dim mypath

mypath = "C:\Users\timog\Dropbox\Brand Page Details\1.Reports\Detailed Reports\"
myfile = Dir(mypath & "*.xlsx")

Do While myfile <> ""
If myfile Like "zz*.xlsx" Then
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "AMS_Import all Results", mypath & myfile, True

End If
myfile = Dir()
Loop
MsgBox "Load Finished"

End Function
Sub Import()

End Sub




Many thanks in advance.

Tim
 

Ranman256

Registered User
Joined
Apr 9, 2015
Messages
3,430
The table should have a field called FileName.
Then right after the Transferspreadsheet command,
Run an update query to fill in the null FileName field with the name.

SSql=" Update table set Filename= '" & MyFile & "' where [filename] is null"
Docmd.runSql sSql
 

archer69

New member
Joined
Jan 17, 2017
Messages
7
Hi Ranman,

Thanks for the quick response.

I've added the code as below but am getting an error with the SSQL statement:

Function Import_Excel()

Dim myfile
Dim mypath

mypath = "C:\Users\timog\Dropbox\Minster Food Brokers\Brand Page Details\1.Reports\Detailed Reports\"
myfile = Dir(mypath & "*.xlsx")
SSql= Update table set Filename = '" & MyFile & "' where [Supplier] is null"

Do While myfile <> ""
If myfile Like "zz*.xlsx" Then
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "AMS_Import all Results", mypath & myfile, True
DoCmd.RunSQL SSql

End If
myfile = Dir()
Loop
MsgBox "Load Finished"

End Function
Sub Import()

End Sub



Sorry, I'm sure there's an easy answer just beyond me at the moment.

Thanks

Tim
 

Minty

AWF VIP
Joined
Jul 26, 2013
Messages
6,543
You need to create the sql string correctly and you need to do it inside your loop.
Your If Then Else is a bit weird and you're not setting your variables correctly (See Red Bits)
Code:
Function Import_Excel()

Dim myfile [COLOR="red"]As String[/COLOR]
Dim mypath [COLOR="Red"]As String[/COLOR]
[COLOR="Red"]Dim sSql as String[/COLOR]

mypath = "C:\Users\timog\Dropbox\Minster Food Brokers\Brand Page Details\1.Reports\Detailed Reports\"
myfile = Dir(mypath & "*.xlsx")

Do While myfile <> ""
	If [COLOR="Green"]NOT [/COLOR]myfile Like "zz*.xlsx" Then
	
		DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "AMS_Import all Results", mypath & myfile, True
		sSql = [COLOR="Red"]"[/COLOR]Update [I][COLOR="Red"]YourTableName [/COLOR][/I]set Filename = '" & MyFile & "' where [Supplier] is null ;"

		Debug.Print sSql
		DoCmd.RunSQL sSql

	End If
	myfile = Dir()
Loop
MsgBox "Load Finished"

End Function
Sub Import()

End Sub
 

archer69

New member
Joined
Jan 17, 2017
Messages
7
Hi Minty,

Thanks for your input.

I've copied and pasted your updated code and changed YourTableName to the table name but it's generated a syntax error at the DoCmd.RunSQL sSql line.

Any thoughts?

As I say, I really don't understand VBA (yet!) and have bodged the code together by copying and pasting from other threads I've found so am not at all surprised it doesn't look great....but it seems to work!

If you've any thoughts on what else I need to update to get this working I'll be very grateful.

Thanks

Tim
 

JHB

Have been here a while
Joined
Jun 17, 2012
Messages
7,720
Show a printscreen of the table structure or post your database with some sample data, zip it.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,662
If I were to attack this problem, I would use the File System Object (FSO) to find the .XLS or .XLSX file. Once I have that specification, it becomes possible to use FSO methods to extract only the name of that file into a separate string, let's call it SupplierString. You don't care about path or type for this purpose.

The next thing I would do is import the spreadsheet into a working table and I would have error traps around this process. The issue is that sometimes you have transfer problems because of spreadsheet formatting inconsistencies. So import to a temporary table first. If there are no errors during the transfer, you are good for the next step.

Remember that string that holds the file's name (but not device, path, or type)? The last step of this is an SQL statement to INSERT INTO the final table, where you have one more field in the final table than you had in the working table. That field is your Supplier, which you say comes from the name of the sheet. I'm using string variable SLQINS to hold the query.

Code:
SQLINS = "INSERT INTO FinalTable (Supplier, other-field-1, other-field-2, ..., last-other field) " & _
        "SELECT '"& Trim(SupplierString) & "', other-field-1, other-field-2, ..., last-other field " & _
        "FROM TemporaryTable ;"

DoCmd.RunSQL SQLINS
As a beginner with Access/VBA, you might be confused. What I did there was break up the steps so that I could pull out the name you needed, then did the import "pure" (i.e. not trying to add anything to the data coming in) so that I could catch errors. That way, if something goes wrong, I stop it BEFORE the final table gets updated. Because you see, it will be a bigger pain in the toches to take out the erroneous data than it would be to never put it there in the first place. Also it is part of Murphy's Law.

Then, once you know your spreadsheet import worked OK, you can append that data to the final table with that constant string for the supplier's name plus the other fields you got from the import. Since you are a beginner, you might have trouble recognizing what syntax is in use there. The string is being build as a quoted string, with the ampersand and underscore symbols saying to "concatenate (join) this string with the next string item, which will be on the next line." The supplier is added to the string by preparing to enclose the string in single-quotes, but since we have to do substitution, we break the string (using double quotes) and use ampersands to do the concatenation.
Therefore that string is

"... SELECT {single-quote}{double-quote}{ampersand} SupplierString {ampersand}{double-quote}{single-quote}, ..."

Also, for future reference, it is OK to show some code when reporting an error but don't say "I've got an error." You say "I've got error number 12344" and include at least some part of the text that goes with the error number. When Access offers a chance to debug, click on that debug button. Then you can tell us, "it highlights the line reading 'DoCmd.RunSQL ....'. That way we see your context, we see the error, and we see the offending line. We can give you MUCH better guidance that way.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom