Form creates new workbook and pulls data

[luke99s]

Is my logic correct? I have a macro running in a workbook that asks the user for the account number. On Click, I want the macro to create and save a new workbook with the account number as file name, then excecute a query to pull data from a database.

The macro ran fine when taking the account number and pasting it on a new sheet in the same workbook. Its failing now that I am trying to get the data onto a new xls. Am I trying something not possible?

[K Dales]

Your logic is OK - there are ways to do this. Would need to see the code to
see why it may not be working. Also, how is it failing? - what happens?
--
- K Dales


"luke99s" wrote:

>
> Is my logic correct? I have a macro running in a workbook that asks the
> user for the account number. On Click, I want the macro to create and
> save a new workbook with the account number as file name, then excecute
> a query to pull data from a database.
>
> The macro ran fine when taking the account number and pasting it on a
> new sheet in the same workbook. Its failing now that I am trying to
> get the data onto a new xls. Am I trying something not possible?
>
>
> --
> luke99s
> ------------------------------------------------------------------------
> luke99s's Profile: http://www.excelforum.com/member.php...o&userid=28016
> View this thread: http://www.excelforum.com/showthread...hreadid=475224
>
>

[luke99s]

Thinking there may be a problem with moving data between workbooks, I've tried to see if I could move it between worksheets. The operation still fails, I now get an error " The desination range is not on the same worksheet that the query table is being created on "

In a nutshell, workbook when opened has only a sheet with a form on it. User enters client account number and hits enter. On click, the account number is pulled and inserted into the query below. Query connects to our database to bring data into new sheet with name the same as the account number. When I do this manually everything is peachy... its the macro that is failing! please help and thanks in advance!

I've pasted a snippet of where my problem seems to be...


<code>
A = custnum.Value
ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = A
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://intranet/POOL_Digest/Digest/MainPage.aspx?ResourceID=1&ResourceName=CustomerSalesByProductsInProductLineByYear&SRN=/All/Digest/Customer/Sales/CustomerSalesByProductsInProductLineByYear\Customer{10" & A & "}&AuxRI=All!" & A & "" _
, Destination:=Range("A1"))
.Name = "Customer{10" & A & "}&AuxRI=All!" & A & ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

</code>