Export to Excel

[Guest]

Our office receives a report in Excel that combines information for 13
different offices. We have been manually splitting the Excel spreadsheet
into 13 different spreadsheets. I am trying to automate that process.

I am using Access and Excel 2003. I have linked the spreadsheet to access
and written the queries to separate the information. Now I want to export
the queries to a spreadsheet (one query per spreadsheet, so it can be emailed
to the individual offices.)

I was able to write a macro that created the spreadsheets, but I am unable
to format the spreadsheets without opening each one. So I wanted to try my
hand at VBA (I have very limited experience with Access VBA, but moderate
experience in Word and Excel VBA). I see information regarding the
transferspreadsheet method, but so far have not had any luck.

Specifically what I want to do is export a query called qryAlex1000Days and
create a spreadsheet called Alex 1000 Days.xls in a folder called
C:\workload\1000 Days. Then I want to format the spreadsheet so that each
column is expanded to fit the information and resave it. I don't need
anything fancy, I just need to have the columns expanded. (then I will need
to do this 12 more times for the other 12 offices (different query names and
create a different file in the same folder listed above.)

Any help would be greatly appreciated.

Elaine

 

[Guest]

Hi Elaine,
I assume you have the names of the offices in a field called off_name in a
table called, let's say, offices and the id_off is the number of the office
(e. g. 1=Alex 1000 Days). Lets' say that in the table offices you have also a
field called qry where you have the name of the query that extract the data
related to the office.
Set fs = CreateObject("Scripting.FileSystemObject")
for i= 1 to 13
'checking if the file already exist. If exist it's deleted
If fs.FileExists("C:\workload\1000 Days\" &
dlookup("off_name","offices","id=" & i) & ".xls")
Then
Kill "C:\workload\1000 Days\" & dlookup("off_name","offices","id=" &
i) & ".xls"
End If
'you export your query to XL
DoCmd.TransferSpreadsheet acexport, acSpreadsheetTypeExcel9
, dlookup("qry","offices","id=" & i) , "C:\workload\1000 Days\" &
dlookup("off_name","offices","id=" & i) & ".xls"
' you autofit the columns in the XL file just created
Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open C:\workload\1000 Days\" &
dlookup("off_name","offices","id=" & i) & ".xls"
ExcelSheet.Workbooks.cells.select
ExcelSheet.Workbooks.Selection.Columns.AutoFit
ExcelSheet.Workbooks.save
ExcelSheet.Quit
Set ExcelSheet = Nothing
next i

I just wrote this code without testing it so perhaps there's some errors...

HTH Paolo