Reference to filepath in Module, Colon creates error

Jewlrose

Registered User.
Local time
Today, 13:44
Joined
Sep 13, 2012
Messages
13
I've done some programming in other languages, but am a complete newbie when it comes to VBA. I needed to send a query to a specific sheet within excel (so it wouldn't save over the entire workbook each time) and found a module online to do so. As I cannot link the site, I will include the code below.

Unfortunately, when I put in my file path I get the following error:

Compile error:
Expected: list separator or )

My file path is the following:

O:\TargetFolder\2012\myfile.xlsx

It highlights the colon as the source of the problem. My guess is that the colon is a protected character. I tried using "\" to escape it, but that didn't help. Is there a different escape character for VBA? Am I on the wrong track, and if so, could someone set me on the right one?

Thanks in advance!!

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to

' strFilePath is the name and path of the file you want to send this data into.

Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
On Error GoTo err_handler


strPath = strFilePath



Set rst = CurrentDb.OpenRecordset(strTQName)


Set ApXL = CreateObject("Excel.Application")



Set xlWBk = ApXL.Workbooks.Open(strPath)


ApXL.Visible = True


Set xlWSh = xlWBk.Worksheets(strSheetName)


xlWSh.Range("A1").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst

xlWSh.Range("A2").CopyFromRecordset rst


xlWSh.Range("1:1").Select
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With


ApXL.Selection.Font.Bold = True


With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With


' selects all of the cells
ApXL.ActiveSheet.Cells.Select


' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit


' selects the first cell to unselect all cells
xlWSh.Range("A1").Select


rst.Close


Set rst = Nothing


Exit_SendTQ2XLWbSheet:
Exit Function


err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Resume Exit_SendTQ2XLWbSheet
End Function
 
As a note, if I put quotes around the second reference to the file path (strPath = strFilePath) so that it reads: strPath = "O:\TargetFolder\2012\myfile.xlsx" , that works fine. However, if I put quotes around it in the first reference (strFilePath As String), it gives me the following error:

Compile error:
Expected: identifier

And it highlights the entire file path (O:\TargetFolder\2012\myfile.xlsx).
 
Got it working!
 
Got it working!

Perhaps for posterity you could elaborate as to what solution you found. Frequently people end up on pages due to similar search keywords, and I suspect they would like just a few more details than: Got it working!
 
I stopped trying to edit the module and instead used a macro to call the module, as was the original module's intent (I later discovered).

copy the entire code (UNCHANGED) into a standard module (not form or report module) and name the module something like basExcelExport (just needs to be different from the procedure name).
Then you would call it in your code or macro using

SendTQ2XLWbSheet "Jack Powell Completions", "NameOfTheWorksheetHere", "PathAndNameOfTheWorkBookHere"
so if the name of the worksheet was MySheet and the path and file was C:\Temp\JackPComp.xlsx" you would use
SendTQ2XLWbSheet "Jack Powell Completions", "MySheet", "C:\TempJackPComp.xlsx"
--Bob Larson @ microsoft.com
 

Users who are viewing this thread

Back
Top Bottom