Help with some VB scripting

[Guest]

Good afternoon everyone. I need help scripting a small snipet of code for an
Access DB. I need to take info from a a table and put it into an Excel file.
I found this in the forums and I am trying to adapt it for my needs. Here is
the script.

INSERT INTO
[Excel 8.0;database=C:\MyWorkbook.xls;].MyBookLevelName
(MyCol1, MyCol2, MyCol3)
SELECT
ColA AS MyCol1,
ColB AS MyCol2,
ColC AS MyCol3
FROM
MyTable

What I think I need to do:
1. Change .MyBookLevelName to .Temp (assuming the workbook name is Temp)
2. Change ColA, ColB, ColC to the field names in the table.
3. Change MyTable to the name of the Access table I want to export from.

So it should look like:

INSERT INTO
[Excel 8.0;database=C:\addresses.xls;].Temp
(First_Name, Last_Name, Zip_Code)
SELECT
First_Name AS MyCol1,
Last_Name AS MyCol2,
Zip_Code AS MyCol3
FROM
Address

Thanks for the help. I really need to take a VB course.

 

[Marshall Barton]

Good afternoon everyone. I need help scripting a small snipet of code for an
Access DB. I need to take info from a a table and put it into an Excel file.
I found this in the forums and I am trying to adapt it for my needs. Here is
the script.

INSERT INTO
[Excel 8.0;database=C:\MyWorkbook.xls;].MyBookLevelName
(MyCol1, MyCol2, MyCol3)
SELECT
ColA AS MyCol1,
ColB AS MyCol2,
ColC AS MyCol3
FROM
MyTable

What I think I need to do:
1. Change .MyBookLevelName to .Temp (assuming the workbook name is Temp)
2. Change ColA, ColB, ColC to the field names in the table.
3. Change MyTable to the name of the Access table I want to export from.

So it should look like:

INSERT INTO
[Excel 8.0;database=C:\addresses.xls;].Temp
(First_Name, Last_Name, Zip_Code)
SELECT
First_Name AS MyCol1,
Last_Name AS MyCol2,
Zip_Code AS MyCol3
FROM
Address

It would be faster if you'd just set up a test with a scrap
workbook and try your code. Then, if you can't figure out
why it didn't work, post a specific question.

What you have looks like it should work, but I think you
misstated what "Temp" is. You already specified that the
workbook is named addresses.xls, Temp is the name of the
worksheet or a predefined named range within the workbook.

Be sure to change MyCol1, etc. to the actual column labels
in the sheet or named range.

 

[Jamie Collins]

DanNeedsHelp wrote ...

Good afternoon everyone. I need help scripting a small snipet of code for an
Access DB. I need to take info from a a table and put it into an Excel file.
I found this in the forums and I am trying to adapt it for my needs. Here is
the script.

INSERT INTO
[Excel 8.0;database=C:\MyWorkbook.xls;].MyBookLevelName
(MyCol1, MyCol2, MyCol3)
SELECT
ColA AS MyCol1,
ColB AS MyCol2,
ColC AS MyCol3
FROM
MyTable

What I think I need to do:
1. Change .MyBookLevelName to .Temp (assuming the workbook name is Temp)
2. Change ColA, ColB, ColC to the field names in the table.
3. Change MyTable to the name of the Access table I want to export from.

Does the destination worksheet already exist and have column headers?
If it does, and it's named MySheet, then:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\temp.xls;].[MySheet$]
(First_Name, Last_Name, Zip_Code)
SELECT
First_Name, Last_Name, Zip_Code
FROM
Address
;

The above assumes the existing Excel column headers match the ones in
your database.

The above also assumes that temp.xls already exists in the root of
your C drive i.e you may need to edit the path. If the workbook and/or
Excel table does not exist i.e. is being created on the fly, you need
the SELECT..INTO syntax:

SELECT
First_Name, Last_Name, Zip_Code
INTO
[Excel 8.0;HDR=Yes;Database=C:\temp.xls;].NewBookLevelName
FROM
Address
;

Feel free to change the Excel table name 'NewBookLevelName' to
something more meaningful.

Thanks for the help. I really need to take a VB course.

Make that a sql course <g>.

Jamie.

--

 

[Jamie Collins]

What you have looks like it should work, but I think you
misstated what "Temp" is. You already specified that the
workbook is named addresses.xls, Temp is the name of the
worksheet or a predefined named range within the workbook.

It couldn't be a worksheet because they require a trailing $ and brackets e.g.

.[WorksheetName$]

Jamie.

--