Form creates new workbook and pulls data
 

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

Form creates new workbook and pulls data
 

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

Form creates new workbook and pulls data
 

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(Wo rksheets.Count)).Name
= A
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://intranet/POOL_Digest/Digest/MainPage.aspx?ResourceID=1&ResourceName=CustomerSa lesByProductsInProductLineByYear&SRN=/All/Digest/Customer/Sales/CustomerSalesByProductsInProductLineByYear\Custome r{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


--
luke99s
------------------------------------------------------------------------
luke99s's Profile: http://www.excelforum.com/member.php...o&userid=28016
View this thread: http://www.excelforum.com/showthread...hreadid=475224