Merging cells in excel before importing into access (1 Viewer)

luzz

Registered User.
Local time
Today, 13:06
Joined
Aug 23, 2017
Messages
346
Hi guys, is it possible to have VBA code that can merge cells in excel before clicking on the import button? As i would like to merge three cells in my excel file into one cell and when i click on import button on my access form, the imported file will only show as one cell instead of three cells.

Below is the code for my import button in vba access

Private Sub cmdImport_Click()
Dim filepath As String
filepath = "C:\Users\Users\Desktop\FabricPODatasheet.xlsx"
DoCmd.TransferSpreadsheet acImport, , "FabricPO", filepath, True
End Sub

Thank you.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:06
Joined
Aug 30, 2003
Messages
36,118
I would probably import the data into a temp table, then use an append query that combines the fields.
 

luzz

Registered User.
Local time
Today, 13:06
Joined
Aug 23, 2017
Messages
346
I would probably import the data into a temp table, then use an append query that combines the fields.

hmm, but the excel file that i will be importing into access will be updated every night so user will have to import data first thing in the morning everyday. If i would take your advise, when user import the data into the temp table, i will have to create a append query every time a user import the new data?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:06
Joined
Aug 30, 2003
Messages
36,118
No, you'd build the query now and just execute it daily. Your daily process would be:

Empty temp table
Import data into temp table
Append into main table
 

luzz

Registered User.
Local time
Today, 13:06
Joined
Aug 23, 2017
Messages
346
No, you'd build the query now and just execute it daily. Your daily process would be:

Empty temp table
Import data into temp table
Append into main table

Is there a shorter way? Like just automate all of this in just one button? as i am doing it for a company and the users who are using this system are not very tech savvy. Therefore, i would like it to be as simple as possible and minimal amount of steps
 

Minty

AWF VIP
Local time
Today, 20:06
Joined
Jul 26, 2013
Messages
10,355
Yes of course you can automate it. That's what you would do with a code sub.

Make three subs, one for each process. Then simply call them one after the other assuming the previous stage completes correctly.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:06
Joined
Aug 30, 2003
Messages
36,118
Is there a shorter way? Like just automate all of this in just one button?

i was suggesting doing it behind a single button. Two lines executing SQL and your import between them.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:06
Joined
Feb 19, 2002
Messages
42,973
If you are linking to the spreadsheet now rather than linking to it, then simply create a query that merges the columns.

Select fld1, fld2, fld3 & " " & fld4 & " " & fld5 As mergedField, fld6, fld7, ....
 

Users who are viewing this thread

Top Bottom