Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-20-2017, 04:01 AM   #1
archer69
Newly Registered User
 
Join Date: Jan 2017
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
archer69 is on a distinguished road
Add file name to table when importing via spreadsheet

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

archer69 is offline   Reply With Quote
Old 01-20-2017, 04:14 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,367
Thanks: 0
Thanked 742 Times in 727 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Add file name to table when importing via spreadsheet

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
Ranman256 is offline   Reply With Quote
Old 01-20-2017, 04:20 AM   #3
archer69
Newly Registered User
 
Join Date: Jan 2017
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
archer69 is on a distinguished road
Re: Add file name to table when importing via spreadsheet

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

archer69 is offline   Reply With Quote
Old 01-20-2017, 05:04 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Add file name to table when importing via spreadsheet

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 As String
Dim mypath As String
Dim sSql as String

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

Do While myfile <> ""
	If NOT myfile Like "zz*.xlsx" Then
	
		DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "AMS_Import all Results", mypath & myfile, True
		sSql = "Update YourTableName 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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 01-20-2017, 10:42 AM   #5
archer69
Newly Registered User
 
Join Date: Jan 2017
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
archer69 is on a distinguished road
Re: Add file name to table when importing via spreadsheet

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
archer69 is offline   Reply With Quote
Old 01-22-2017, 06:13 AM   #6
archer69
Newly Registered User
 
Join Date: Jan 2017
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
archer69 is on a distinguished road
Re: Add file name to table when importing via spreadsheet

Bump to see if anyone can help me...

Thanks

Tim
archer69 is offline   Reply With Quote
Old 01-22-2017, 08:06 AM   #7
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Add file name to table when importing via spreadsheet

Show a printscreen of the table structure or post your database with some sample data, zip it.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 01-22-2017, 09:49 AM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,501
Thanks: 92
Thanked 1,668 Times in 1,549 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Add file name to table when importing via spreadsheet

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Tags
filename , import , spreadsheet , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing xml file into a table sven2 Modules & VBA 2 04-05-2010 09:54 AM
Importing file names in a Table Danielf Modules & VBA 10 01-25-2010 12:22 PM
Importing Excel Spreadsheet to a table katz3yez Modules & VBA 5 02-07-2007 10:26 PM
A tough one - Importing a file into a NEW table Macsterling Tables 3 05-26-2006 08:46 PM
Importing File into Table IceGirlAnne Modules & VBA 1 07-17-2002 10:50 AM




All times are GMT -8. The time now is 12:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World