I have a spreadsheet which, when opened for the first time, creates a web
query on the fly and imports some data into a worksheet. My problem is
that the datatype for all the cells are automatically set to "General",
but this causes some problems with formatting so I would like to be able
to specify each columns data type separately. I can specify them in the
worksheet, but the web query results will overwrite them. Is there some
way to tell the web query only to overwrite the values, not the formatting
specifications of the worksheet columns? I tried setting
QueryTable.PreserveColumnInfo to true, but that gives an error:

Run-time error '1004':

Application-defined or object-defined error.

I can set QueryTable.PreserveFormatting to true but that doesn't do anything.
Here is the code that builds the web query:

Private Sub Workbook_Open()

Dim strURL As String
Dim Q As QueryTable
Dim strName As String
Dim shtData As Excel.Worksheet
Dim hlpData As Excel.Worksheet
Dim ws As Worksheet
Dim piv As PivotTable
Dim i As Long

Set shtData = Application.Worksheets("Data")
Set hlpData = Application.Worksheets("Help")

strName = hlpData.Cells(4, 5)

If shtData.QueryTables.Count = 0 Then
'Not yet created web query

strURL = hlpData.Cells(5, 5) & strName

Set Q = shtData.QueryTables.Add(Connection:="URL;" & strURL, _
Destination:=shtData.Range("A1"))

shtData.Unprotect

With Q
.RefreshStyle = xlOverwriteCells
.WebDisableDateRecognition = True

.Refresh

'Create named range called "DATA" for the resultset
.ResultRange.Name = "DATA"
End With

shtData.Protect
End If

Set Q = Nothing
Set shtData = Nothing
Set hlpData = Nothing

End Sub