FollowHyperLink and continue code

S

Song Su

I have following code to export from query to Excel and open Excel file
(Application.FollowHyperlink)

DoCmd.SetWarnings False
Dim stDocName As String
DoCmd.OpenQuery "qryUnionToTable"
stDocName = "MultipleSections"
DoCmd.TransferSpreadsheet acExport, , stDocName, (Environ("homepath") &
"\My Documents\MultipleSections.xls")
DoCmd.SetWarnings True
Application.FollowHyperlink (Environ("homepath") & "\My
Documents\MultipleSections.xls"), , True

After Excel opened, I want to format all cells to number with 1 decimal like
below. How to continue these 3 lines after Application.FollowHyperlink? What
do I need in between these 2 blocks?

Cells.Select
Selection.NumberFormat = "0.0"
Range("A1").Select
 
H

hmadyson

Rather than follow hyperlink I believe that you need to start using the
Microsoft Excel class object. Unfortunately you will not be able to use
Selection since you do not get a chance to make changes here, but you can set
the range's format

Here are some lines to open the workbook

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheets


Set xl = New Excel.Application
Set wb = xl.Workbooks.Open(Environ("homepath") & "\My
Documents\MultipleSections.xls")
Set ws = wb.Worksheets(stdocname)
ws.Range("A1:B5").NumberFormat = "0.0"

Please let me know I can provide more assistance.
 
S

Song Su

Here is my modified code. I use Microsoft Excel 11.0 reference. When I run
it, the error message is "Data Type mismatch"

DoCmd.SetWarnings False
Dim stDocName As String
DoCmd.OpenQuery "qryUnionToTable"
stDocName = "MultipleSections"
DoCmd.TransferSpreadsheet acExport, , stDocName, (Environ("UserProfile")
& "\My Documents\MultipleSections.xls")
DoCmd.SetWarnings True
'Application.FollowHyperlink (Environ("UserProfile") & "\My
Documents\MultipleSections.xls"), , True
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheets


Set xl = New Excel.Application
Set wb = xl.Workbooks.Open(Environ("UserProfile") & "\My
Documents\MultipleSections.xls")
Set ws = wb.Worksheets("MultipleSections")
ws.Range("D2:B55000").NumberFormat = "0.0"
 
H

hmadyson

which line gives you the data type mismatch?

Song Su said:
Here is my modified code. I use Microsoft Excel 11.0 reference. When I run
it, the error message is "Data Type mismatch"

DoCmd.SetWarnings False
Dim stDocName As String
DoCmd.OpenQuery "qryUnionToTable"
stDocName = "MultipleSections"
DoCmd.TransferSpreadsheet acExport, , stDocName, (Environ("UserProfile")
& "\My Documents\MultipleSections.xls")
DoCmd.SetWarnings True
'Application.FollowHyperlink (Environ("UserProfile") & "\My
Documents\MultipleSections.xls"), , True
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheets


Set xl = New Excel.Application
Set wb = xl.Workbooks.Open(Environ("UserProfile") & "\My
Documents\MultipleSections.xls")
Set ws = wb.Worksheets("MultipleSections")
ws.Range("D2:B55000").NumberFormat = "0.0"
Click to expand...
 
S

Song Su

I've no idea which line give 'type mismatch' error message. The only
candidate is the last line?
 
H

hmadyson

If you would like, you can send your project to
k--a--r--e--n--y--y--y--1--at--c--o--m--c--a--s--t--dot--n--e--t
and I will look at it.
 
Top