I am trying to open an external data file to write and store data from my current worksheet. I put in msgbox to ensure data is what I want. The code below places the desired data in the desired ranges, but will not save the file with that data in it. Must be something simple that I am missing. I do not want a prompt in saving the data.
Private Sub cmdSave_Click()
Dim objApp, objWbs, objWorkbook, objSheet
Set objApp = CreateObject("Excel.Application")
Set objWbs = objApp.Workbooks
objApp.Visible = True
Set objWorkbook = objWbs.Open("FilePath and Name")
Set objSheet = objWorkbook.Sheets("Saved")
If Rcount < 1 Then
Rcount = 1
Else
Rcount = Rcount + 40
End If
MsgBox Rcount
MsgBox Worksheets("Sheet1").Range("A2")
objSheet.Range("U" & Rcount).Value = Rcount
objSheet.Range("Q" & Rcount & ":R" & Rcount + 10).value = Worksheets("Sheet1").Range("A2:B12")
objSheet.Range("A" & Rcount & ":D" & Rcount + 23).value = Worksheets("Sheet2").Range("D3:G26")
MsgBox objSheet.Range("Q" & Rcount + 10)
Application.DisplayAlerts = False
objWorkbook.Close SaveChanges = True
Set objSheet = Nothing
Set objWbs = Nothing
Set objWorkbook = Nothing
Set objApp = Nothing
Application.DisplayAlerts = True
MsgBox "Configuration stored"
End Sub
As a note, when I reverse these two ranges it moves from the external file to my current file correctly as shown below.
Worksheets("Sheet1").Range("A2:B12") = objSheet.Range("Q" & Rcount & ":R" & Rcount + 10) .Value
As I said, I can see with the message box break that the data is in the correct place before the file is closed. It just won't save that data. Those ranges that were filled before closing are blank when I reopen the external file
First three msgboxes will not be needed once complete. They are just there to make sure information is right at that point, which it is. Only the fourth box will remain once I can get this to save the data to confirm the action to the user..
Bookmarks