Make Table Query

[rirus]

I have several Excel Spreadsheets linked in an Access DB that I want to
combine into one table. All colunmn headers are identical in the Worksheets.
Manually, I can create a Make Table Query on the first linked spreadsheet,
selecting all fields. This creates the New Table. All other spreadhseets I
use an Append to Table Query. I have to do this each month for reporting
purposes. Since the table is for one months worth of data, to eleviate
confusion I create a new table each month.

Question:
I am wondering if there is a way I could do this with VBA? Since all the
header fields are the same in the spreadsheets I could use the select *
statement in the query and then change the table names accordingly.

I think all I need is the basic CreateTableQuery and AppendQueryQuery VBA
code. My understanding of VBA code is ok... I am no expert. Any help would be
appreciated.

thanks,

Rirus

 

[Jeff Boyce]

"... create a new table each month..." is a left-over strategy from your
spreadsheets.

Access is a relational database, and both you and Access will have to work
overtime to figure out how to handle 'sheet data.

Instead, append all the data to a single table, and add one field - maybe
something like [DateAdded]. Now you have a set of data that is easy to keep
up to date and easy to query.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MGFoster]

I have several Excel Spreadsheets linked in an Access DB that I want to
combine into one table. All colunmn headers are identical in the Worksheets.
Manually, I can create a Make Table Query on the first linked spreadsheet,
selecting all fields. This creates the New Table. All other spreadhseets I
use an Append to Table Query. I have to do this each month for reporting
purposes. Since the table is for one months worth of data, to eleviate
confusion I create a new table each month.

Question:
I am wondering if there is a way I could do this with VBA? Since all the
header fields are the same in the spreadsheets I could use the select *
statement in the query and then change the table names accordingly.

I think all I need is the basic CreateTableQuery and AppendQueryQuery VBA
code. My understanding of VBA code is ok... I am no expert. Any help would be
appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should include the month date in the table and put all the data into
one table, NOT one table per month. I've set that up in the following
code - named the month date "work_month."

You'll have to use both VBA & SQL to create the table and insert
(append) data.

Create a form with 2 CommandButtons and 1 TextBox. Name the TextBox
"txtMonthStart." Make it a date input by putting the following in the
Control's Input Mask property:

99/99/0000;0;_

In one CommandButton name it "MakeTable" and another named "Append." In
the Click event of both buttons put [Event Procedure].

For the MakeTable CommandButton hit the Build button (to the right of
the Click event property field). It will produce the VBA like this:

Private Sub MakeTable_Click()

End Sub

Put the following code in it:

Private Sub MakeTable_Click()

' make sure you change the name of the Access table.

const TBL = "Excel Table Name"

const SQL = "SELECT #|1# As work_month, * INTO " & _
"[Access Table Name] FROM " & TBL

dim strSQL as string

on error goto err_

' Put in the start of month date
strsql = Replace(sql,"|1",Me!txtMonthStart)

currentdb.execute sql, dbfailonerror

exit_:
exit sub

err_:
msgbox "Error: " & err.description, vbexclamation

End Sub

I don't know how many other other spreadsheets (s/s) you'll be
importing. What I'd do is put the names of the other Excel linked
tables in a table and, in the VBA routine, read the names in a loop &
import data from each s/s.

The table that holds the linked Excel table names would be "ExcelNames"
and it looks like this (you can use the following DDL code to create the
table - put it in a Query's SQL view & run it):

CREATE TABLE ExcelNames (
worksheet_name VARCHAR(25) NOT NULL PRIMARY KEY
);

If the excel table names are longer than 25 characters change the 25 in
VARCHAR(25) to whatever number suits.

For the "Append" CommandButton set up its click event like the MakeTable
click event property. Then put this code in:

Private Sub Append_Click()

' the table that holds the names of all
' the Excel s/s that you are importing.
const TBL = "ExcelNames"

' this is the SQL statement that will append
' the s/s to the Access table.
' make sure you change the name of the Access table.
const SQL = "INSERT INTO [Access Table Name] " & _
"SELECT #|1#, * FROM |2"

on error goto err_

dim db as dao.database, rs as dao.recordset
dim strSQL as string

' set up to read the s/s names
set db = currentdb
set rs = db.openrecordset(TBL)

' loop thru each s/s name & import its data
do while not rs.eof

' put the month start date in the SQL string
strSQL = Replace(SQL, "|1", Me!txtMonthStart)

' put the name of the s/s in the SQL string
strSQL = Replace(strSQL, "|2", rs!worksheet_name)

' run the insert command (the SQL string)
currentdb.execute strSQL, dbfailonerror

' get the next s/s to import
rs.movenext

loop

exit_:
on error resume next
set rs = nothing
set db = nothing
exit sub

err_:
msgbox "Error: " & err.description, vbexclamation
resume exit_

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlfO4YechKqOuFEgEQLbOgCfW9jemjwR9xV+Bvx9ausfzeWBgv4An1Fh
sHpeOIaeOYUaHBsVs0B1JI4g
=6ldS
-----END PGP SIGNATURE-----

 

[rirus]

Thank you all for your quick replies... that is awesome.

Looking at your reply (Code) I think I can insert the SQL piece into code
that I have created to link the spreadsheets from a directory. It is in a do
loop pulling the Spreadsheet names from a directory and linking them, one
spreadsheet at a time. I think I can run the SQL statement to build the table
the first time through and then add the append query to every linked
spreadsheet after that.

The strTableName is the Excel File Name which is also the linked table
name in Access
The Access table that the linked table data is going in to is called
DataReport
I don't need the date added to the table as there are already multiple
dates in the spreadsheet.

ONE PROBLEM: How can I change the Const TBL = "Excel Table Name" to each
Spreadsheet name (strTableName)

Code below is a shortend version which fails. I added it to give you and idea
of what I am doing. I think all I need is to fix the Const = TBL "Excell File
Name" replacing "Excel File Name" with the string strTableName. I tried that
and it didn't like it. It needs a Const value and I am not sure how do that.

CODE:

Sub LinkSheets()
Dim strTableName as string
Dim runNumb as integer

On Error GoTo err_
DirToSearch = "C:\Reports"
strTableName = Dir(DirToSearch & "\" & "*.xls")

DoCmd.SetWarnings False
err.Clear

runNumb = 0
Do Until strTableName = ""

TblNme = "C:\Reports\" & strTableName

DoCmd.TransferSpreadsheet acLink, 8, strTableName, TblNme, True

If runNumb = 0 then ' first time through
' Your code I added
Const TBL = "Excel Table Name"

Const SQL = "SELECT * INTO DataReport FROM " & TBL

Dim strSQL As String

CurrentDb.Execute SQL, dbFailOnError
Else
' Run the append query

End if

NextFle = Dir()
Loop

goto exit_

err_:
MsgBox "Error: " & err.Description
Goto exit_

exit_:
Exit Sub

End Sub

I have several Excel Spreadsheets linked in an Access DB that I want to
combine into one table. All colunmn headers are identical in the Worksheets.

[quoted text clipped - 12 lines]

code. My understanding of VBA code is ok... I am no expert. Any help would be
appreciated.

You should include the month date in the table and put all the data into
one table, NOT one table per month. I've set that up in the following
code - named the month date "work_month."

You'll have to use both VBA & SQL to create the table and insert
(append) data.

Create a form with 2 CommandButtons and 1 TextBox. Name the TextBox
"txtMonthStart." Make it a date input by putting the following in the
Control's Input Mask property:

99/99/0000;0;_

In one CommandButton name it "MakeTable" and another named "Append." In
the Click event of both buttons put [Event Procedure].

For the MakeTable CommandButton hit the Build button (to the right of
the Click event property field). It will produce the VBA like this:

Private Sub MakeTable_Click()

End Sub

Put the following code in it:

Private Sub MakeTable_Click()

' make sure you change the name of the Access table.

const TBL = "Excel Table Name"

const SQL = "SELECT #|1# As work_month, * INTO " & _
"[Access Table Name] FROM " & TBL

dim strSQL as string

on error goto err_

' Put in the start of month date
strsql = Replace(sql,"|1",Me!txtMonthStart)

currentdb.execute sql, dbfailonerror

exit_:
exit sub

err_:
msgbox "Error: " & err.description, vbexclamation

End Sub

I don't know how many other other spreadsheets (s/s) you'll be
importing. What I'd do is put the names of the other Excel linked
tables in a table and, in the VBA routine, read the names in a loop &
import data from each s/s.

The table that holds the linked Excel table names would be "ExcelNames"
and it looks like this (you can use the following DDL code to create the
table - put it in a Query's SQL view & run it):

CREATE TABLE ExcelNames (
worksheet_name VARCHAR(25) NOT NULL PRIMARY KEY
);

If the excel table names are longer than 25 characters change the 25 in
VARCHAR(25) to whatever number suits.

For the "Append" CommandButton set up its click event like the MakeTable
click event property. Then put this code in:

Private Sub Append_Click()

' the table that holds the names of all
' the Excel s/s that you are importing.
const TBL = "ExcelNames"

' this is the SQL statement that will append
' the s/s to the Access table.
' make sure you change the name of the Access table.
const SQL = "INSERT INTO [Access Table Name] " & _
"SELECT #|1#, * FROM |2"

on error goto err_

dim db as dao.database, rs as dao.recordset
dim strSQL as string

' set up to read the s/s names
set db = currentdb
set rs = db.openrecordset(TBL)

' loop thru each s/s name & import its data
do while not rs.eof

' put the month start date in the SQL string
strSQL = Replace(SQL, "|1", Me!txtMonthStart)

' put the name of the s/s in the SQL string
strSQL = Replace(strSQL, "|2", rs!worksheet_name)

' run the insert command (the SQL string)
currentdb.execute strSQL, dbfailonerror

' get the next s/s to import
rs.movenext

loop

exit_:
on error resume next
set rs = nothing
set db = nothing
exit sub

err_:
msgbox "Error: " & err.description, vbexclamation
resume exit_

End Sub

HTH,

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On further reflection you can use just the TransferSpreadsheet method to
get the data in the target table. You do not need to run consecutive
INSERT INTO statements. Do it like this:


Sub GetExcelFiles(strAccess as string, strDir as string)

' In:
' strAccess - the name of the Access table to import the
' s/s into.
' strDir - name of the directory to get Excel files.
'

On Error GoTo err_

Dim strExcel as string

' let user know we're working
docmd.hourglass true

strExcel = Dir(strDir & "\" & "*.xls")

do while strExcel <> ""

' First loop will import data into new table (making it).
' Following loops will import the other s/s into the new table.

' Set up filename for FileName parameter.
strExcel = strDir & "\" & strExcel

DoCmd.TransferSpreadsheet acLink, 8, strAccess, strExcel, True

' get the next Excel filename
strExcel = Dir

loop

docmd.hourglass false

msgbox "Excel Import Completed", vbinformation, "Import Excel"

exit sub

err_:
docmd.hourglass false
msgbox "Error: " & err.description, vbexclamation, "Import Excel"

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlqxqYechKqOuFEgEQJrdgCghuaPKt+Rw32o6UrWUpJhgiUTZkwAoOSM
cYertDHCs+mFfUJ02XaPD8Hp
=T619
-----END PGP SIGNATURE-----

Thank you all for your quick replies... that is awesome.

Looking at your reply (Code) I think I can insert the SQL piece into code
that I have created to link the spreadsheets from a directory. It is in a do
loop pulling the Spreadsheet names from a directory and linking them, one
spreadsheet at a time. I think I can run the SQL statement to build the table
the first time through and then add the append query to every linked
spreadsheet after that.

The strTableName is the Excel File Name which is also the linked table
name in Access
The Access table that the linked table data is going in to is called
DataReport
I don't need the date added to the table as there are already multiple
dates in the spreadsheet.

ONE PROBLEM: How can I change the Const TBL = "Excel Table Name" to each
Spreadsheet name (strTableName)

Code below is a shortend version which fails. I added it to give you and idea
of what I am doing. I think all I need is to fix the Const = TBL "Excell File
Name" replacing "Excel File Name" with the string strTableName. I tried that
and it didn't like it. It needs a Const value and I am not sure how do that.

CODE:

Sub LinkSheets()
Dim strTableName as string
Dim runNumb as integer

On Error GoTo err_
DirToSearch = "C:\Reports"
strTableName = Dir(DirToSearch & "\" & "*.xls")

DoCmd.SetWarnings False
err.Clear

runNumb = 0
Do Until strTableName = ""

TblNme = "C:\Reports\" & strTableName

DoCmd.TransferSpreadsheet acLink, 8, strTableName, TblNme, True

If runNumb = 0 then ' first time through
' Your code I added
Const TBL = "Excel Table Name"

Const SQL = "SELECT * INTO DataReport FROM " & TBL

Dim strSQL As String

CurrentDb.Execute SQL, dbFailOnError
Else
' Run the append query

End if

NextFle = Dir()
Loop

goto exit_

err_:
MsgBox "Error: " & err.Description
Goto exit_

exit_:
Exit Sub

End Sub

I have several Excel Spreadsheets linked in an Access DB that I want to
combine into one table. All colunmn headers are identical in the Worksheets.

[quoted text clipped - 12 lines]

code. My understanding of VBA code is ok... I am no expert. Any help would be
appreciated.

You should include the month date in the table and put all the data into
one table, NOT one table per month. I've set that up in the following
code - named the month date "work_month."

You'll have to use both VBA & SQL to create the table and insert
(append) data.

Create a form with 2 CommandButtons and 1 TextBox. Name the TextBox
"txtMonthStart." Make it a date input by putting the following in the
Control's Input Mask property:

99/99/0000;0;_

In one CommandButton name it "MakeTable" and another named "Append." In
the Click event of both buttons put [Event Procedure].

For the MakeTable CommandButton hit the Build button (to the right of
the Click event property field). It will produce the VBA like this:

Private Sub MakeTable_Click()

End Sub

Put the following code in it:

Private Sub MakeTable_Click()

' make sure you change the name of the Access table.

const TBL = "Excel Table Name"

const SQL = "SELECT #|1# As work_month, * INTO " & _
"[Access Table Name] FROM " & TBL

dim strSQL as string

on error goto err_

' Put in the start of month date
strsql = Replace(sql,"|1",Me!txtMonthStart)

currentdb.execute sql, dbfailonerror

exit_:
exit sub

err_:
msgbox "Error: " & err.description, vbexclamation

End Sub

I don't know how many other other spreadsheets (s/s) you'll be
importing. What I'd do is put the names of the other Excel linked
tables in a table and, in the VBA routine, read the names in a loop &
import data from each s/s.

The table that holds the linked Excel table names would be "ExcelNames"
and it looks like this (you can use the following DDL code to create the
table - put it in a Query's SQL view & run it):

CREATE TABLE ExcelNames (
worksheet_name VARCHAR(25) NOT NULL PRIMARY KEY
);

If the excel table names are longer than 25 characters change the 25 in
VARCHAR(25) to whatever number suits.

For the "Append" CommandButton set up its click event like the MakeTable
click event property. Then put this code in:

Private Sub Append_Click()

' the table that holds the names of all
' the Excel s/s that you are importing.
const TBL = "ExcelNames"

' this is the SQL statement that will append
' the s/s to the Access table.
' make sure you change the name of the Access table.
const SQL = "INSERT INTO [Access Table Name] " & _
"SELECT #|1#, * FROM |2"

on error goto err_

dim db as dao.database, rs as dao.recordset
dim strSQL as string

' set up to read the s/s names
set db = currentdb
set rs = db.openrecordset(TBL)

' loop thru each s/s name & import its data
do while not rs.eof

' put the month start date in the SQL string
strSQL = Replace(SQL, "|1", Me!txtMonthStart)

' put the name of the s/s in the SQL string
strSQL = Replace(strSQL, "|2", rs!worksheet_name)

' run the insert command (the SQL string)
currentdb.execute strSQL, dbfailonerror

' get the next s/s to import
rs.movenext

loop

exit_:
on error resume next
set rs = nothing
set db = nothing
exit sub

err_:
msgbox "Error: " & err.description, vbexclamation
resume exit_

End Sub

HTH,

 

[rirus via AccessMonster.com]

Yeah, I got it to work but I had to change the, "DoCmd.TransferSpreadsheet
acLink, 8, strAccess, strExcel, True" to "DoCmd.TransferSpreadsheet
acImportDelim, 8, strAccess, strExcel, True".

Uising the acLink, Access creates a file for each link and puts a 1, 2, 3...
etc. after each linked table name.

Thank you MGFoster for all your help on this... appreciate it.


Rirus

On further reflection you can use just the TransferSpreadsheet method to
get the data in the target table. You do not need to run consecutive
INSERT INTO statements. Do it like this:

Sub GetExcelFiles(strAccess as string, strDir as string)

' In:
' strAccess - the name of the Access table to import the
' s/s into.
' strDir - name of the directory to get Excel files.
'

On Error GoTo err_

Dim strExcel as string

' let user know we're working
docmd.hourglass true

strExcel = Dir(strDir & "\" & "*.xls")

do while strExcel <> ""

' First loop will import data into new table (making it).
' Following loops will import the other s/s into the new table.

' Set up filename for FileName parameter.
strExcel = strDir & "\" & strExcel

DoCmd.TransferSpreadsheet acLink, 8, strAccess, strExcel, True

' get the next Excel filename
strExcel = Dir

loop

docmd.hourglass false

msgbox "Excel Import Completed", vbinformation, "Import Excel"

exit sub

err_:
docmd.hourglass false
msgbox "Error: " & err.description, vbexclamation, "Import Excel"

End Sub

HTH,

Thank you all for your quick replies... that is awesome.

[quoted text clipped - 194 lines]