Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue With Save As
Hi,
I am trying to export an access table to an excel workbook using vba. At the end I would like to prompt the user with a save as dialog box so that the user can choose the corresponding path where the excel spreadsheet will be saved. However I do not know how to prompt the user with a save as dialog prompt. I appreciate any help. Thanks. CODE: Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Add 'Delete the default worksheets leaving only one, which we grab On Error Resume Next objExcel.DisplayAlerts = False For Each objWorksheet In objWorkbook.Worksheets objWorksheet.Delete Next objWorksheet objExcel.DisplayAlerts = False On Error GoTo 0 Set objWorksheet = objWorkbook.Worksheets(1) 'END Delete the default worksheets leaving only one, which we grab Do 'Check if a new worksheet needs to be added and rename If objWorksheet Is Nothing Then Set objWorksheet = objWorkbook.Worksheets.Add End If lngSheets = lngSheets + 1 objWorksheet.Name = strcDataSheetPrefix & Format$(lngSheets, "00") 'END Check if a new worksheet needs to be added and rename 'Write the field names and determine where to output data & _ and how many rows. For lngCounter = 0 To rstData.Fields.Count - 1 objWorksheet.Cells(1, lngCounter + 1) = rstData.Fields(lngCounter).Name Next lngCounter Set objRange = objWorksheet.Range("A2") 'END Write the field names ... 'Output the data, CopyFromRecordset moves the cursor in rstData after copy objRange.CopyFromRecordset rstData, lngRows DoEvents 'END Output the data... 'This will tell the loop to create a new worksheet Set objWorksheet = Nothing Loop Until rstData.EOF rstData.Close Set rstData = Nothing Set objRange = Nothing objWorkbook.SaveAs "C:\SFDB\bak\_0__AccessExcel\export.xls" objWorkbook.Close Set objWorkbook = Nothing objExcel.Quit Set objExcel = Nothing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue With Save As
Jack;435311 Wrote: Hi, I am trying to export an access table to an excel workbook using vba. At the end I would like to prompt the user with a save as dialog box so that the user can choose the corresponding path where the excel spreadsheet will be saved. However I do not know how to prompt the user with a save as dialog prompt. I appreciate any help. Thanks. CODE: Check out *GetSaveAsFilename *method in Excel help -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120845 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue With Save As
Thanks p45cal for the help. I appreciate it.
"p45cal" wrote: Jack;435311 Wrote: Hi, I am trying to export an access table to an excel workbook using vba. At the end I would like to prompt the user with a save as dialog box so that the user can choose the corresponding path where the excel spreadsheet will be saved. However I do not know how to prompt the user with a save as dialog prompt. I appreciate any help. Thanks. CODE: Check out *GetSaveAsFilename *method in Excel help -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120845 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save as issue | Excel Discussion (Misc queries) | |||
File Save/Save As Issue | Setting up and Configuration of Excel | |||
save as issue | Excel Programming | |||
Excel Save Issue | Excel Programming | |||
Save As issue | Excel Programming |