Help with some VB scripting

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
DanNeedsHelp said:
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
Click to expand...


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.
 
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
Click to expand...

That's mine said:
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.
Click to expand...

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.
Click to expand...

Make that a sql course <g>.

Jamie.

--
 
Marshall Barton said:
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.
Click to expand...

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

.[WorksheetName$]

Jamie.

--
 
Back
Top