By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,078 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,078 IT Pros & Developers. It's quick & easy.

Access - Excel format

P: 1
I am very new to all this technical stuff, but I am trying to format an excel spreadsheet via access vba but getting an error. Can some one please advice?
My code is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2. DoCmd.Hourglass True
  3. DoCmd.SetWarnings False
  4.     'On Error GoTo ErrHandler:
  5. ' *************** RUN EUR RI DATA RECON ********************
  6.  
  7.     Dim objXL As Object
  8.     Dim strWin As String
  9.     Dim sglRep As Single
  10.     Dim intPos As Integer
  11.     'Set newName = Sheets("Data")
  12.     'newName.Visible
  13.  
  14.     strFile = "P:\test.xls"
  15.  
  16.     If strFile = "" Then Exit Sub
  17.  
  18.     'Saves changes and closes the speradsheet
  19.     Set objXL = GetObject(strFile)
  20.     objXL.Close savechanges:=True
  21.  
  22.     'Transfers the data to the Excel tabs depending on the file being updated
  23.     'DoCmd.TransferSpreadsheet acExport, 8, "tbl_2004 UW Data", strFile, True, "2004 UWY"
  24.     DoCmd.TransferSpreadsheet acExport, 8, "test", strFile, True, "test"
  25.  
  26.  
  27.  
  28.     Set objXL = GetObject(strFile)
  29.     objXL.Application.Visible = True
  30.  
  31.     strWin = strFile
  32.     intPos = 1
  33.  
  34.     'Finds name of window
  35.     Do Until intPos = 0
  36.  
  37.         intPos = InStr(strWin, "\")
  38.         strWin = Right(strWin, Len(strWin) - intPos)
  39.  
  40.     Loop
  41.  
  42.     'Makes spreadsheet current and unhidden
  43.     objXL.Parent.Windows(strWin).Activate
  44.     objXL.Parent.Windows(strWin).Visible = True
  45.  
  46.     'Sets file to read only
  47.     'If strFile = "P:\AgedDebtors\CashDataMaster.xls" Then
  48.      '   objXL.Application.DisplayAlerts = False
  49.       '  objXL.ChangeFileAccess Mode:=xlReadOnly
  50.         'Sheets("Data").Select
  51.         'Cells.Select
  52.     'Cells.EntireColumn.AutoFit
  53.     'Range("A1").Select
  54.     'Range("A1:R48").Sort Key1:=Range("O2"), Order1:=xlDescending, Header:= _
  55.      '   xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
  56.       '  DataOption1:=xlSortNormal
  57.     'End If
  58.  
  59.     strFile = ""
  60.     strWin = ""
  61.     sglRep = 0
  62.     DoCmd.Hourglass False
  63.     DoCmd.SetWarnings True
  64.  
  65.     Range("a:c").select
  66.  
  67.     'MsgBox "Run Complete"
  68.  
  69. 'ErrHandler:
  70.     'MsgBox (Err.Source & " - " & Err.Description)
  71. End Sub

Thanks in advance.
Oct 9 '08 #1
Share this Question
Share on Google+
2 Replies


puppydogbuddy
Expert 100+
P: 1,923
To use Linq's expression, Carnac the Magnificent is not available at this site, and we can't read your mind....LOL! Please provide more details. What is the error you are getting? Could the error be related to some of the code you have commented out?
Oct 9 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi. I have added code tags before and after your code section, which makes it much easier to refer to specific lines.

As PDog has said, you really must tell us what is going wrong and what kind of error message you are getting - we should not be guessing.

As an observation, I can say for certain that the commented out code at lines 50-54 will not function reliably in Access, as you are not using the automation object variable you have defined (objXL) to communicate with the Excel instance. Referring to ranges and so on works within Excel itself without qualification by the worksheet object, but not when running as automation code from Access.

ALL references to ranges and so on will need to be qualified with the current object and worksheet variables. For example,

ObjXL.ActiveSheet.Range("A1").Select

and not

Range("A1").Select

As an aside, it is much more efficient not to select ranges and cells before setting them to specific values or formats; refer directly to the range or cell object to set its properties.

Many of these problems arise when using the Macro Recorder in Excel to record actions. The code recorded is neither truly portable when used in an automation context nor particularly efficient.

-Stewart
Oct 9 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.